SQL语句

最新推荐文章于 2025-07-19 15:12:22 发布

原创最新推荐文章于 2025-07-19 15:12:22 发布 · 627 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #join #c

SQL 专栏收录该内容

30 篇文章

订阅专栏

本文介绍了使用SQL进行数据更新的两种方法及一种复杂的商品销售数据汇总查询案例。通过具体的SQL语句展示如何从不同表中获取并更新数据，以及如何进行商品销售数据的汇总分析，包括数量、销售额等关键指标的计算。

//
UPDATE 表1 SET KHNY=(SELECT B.XSNY FROM OPENDATASOURCE('SQLOLEDB','Data Source=......;User ID=......;Password=......').[YDZD].[DBO].表2 B WHERE B.DWDH=表1.DWDH)

//
UPDATE 表1 SET A.KHNY=B.XSNY FROM 表1 A,表2 B WHERE A.DWDH=B.DWDH

//
SELECT E.商品编号 AS 商品编号,C.PM_S AS 商品名称,SUM(E.数量) AS 数量小计,SUM(E.含税销售额) AS 含税销售额小计,SUM(E.购进金额) AS 购进金额小计,
SUM(E.毛利) AS 毛利小计,CASE WHEN SUM(E.含税销售额)= 0 THEN 0 ELSE ROUND(SUM(E.含税销售额-E.购进金额)/SUM(E.含税销售额)*100,2) END AS 毛利率 FROM
(SELECT A.DWDH_S AS 单位代号,A.DWMC_S AS 单位名称,A.BH_S AS 商品编号,A.PM_S AS 商品名称,A.SL_S AS 数量,A.XE_S AS 含税销售额,
   ISNULL(B.JJ,0) AS 进价,ROUND(ISNULL(B.JJ,0)*A.SL_S,2) AS 购进金额, A.XE_S-ROUND(ISNULL(B.JJ,0)*A.SL_S,2) AS 毛利,
   CASE WHEN A.XE_S= 0 THEN 0 ELSE ROUND((A.XE_S-ROUND(ISNULL(B.JJ,0)*A.SL_S,2))/A.XE_S*100,2) END AS 毛利率,
   CONVERT(CHAR,A.RQ_S,111) AS 日期, A.SE_S AS SE,A.DJH_S AS DJH,A.PH_S AS PH
   FROM SQLZCYXS AS A
   LEFT JOIN TANG.DBO.S_GJJE_SET AS B ON A.BH_S=B.BH
   UNION ALL
   ......
   ) E LEFT JOIN SQLKC AS C ON E.商品编号=C.BH_S
GROUP BY E.商品编号,C.PM_S"