SQL语句

[tansx]

主表
单号 日期
001 2009-01-01
002 2009-01-02
001J1 2009-01-03
001J2 2009-01-03
002J1 2009-01-03
002J2 2009-01-08
002J3 2009-01-09
明细表
单号 货物名称
001 大米
001 萝卜
002 香菇
002 黄豆
001J1 面条
001J1 白菜
001J2 白菜
001J2 橙
002J1 白菜
002J1 橙
002J2 大米
002J3 香菇

说明: 带J的单号表示是补货即补货单号(如原001单中货物不够,需要补充货物的话,则补充货物的单号是
001J1,J1表示第一次补货,J2表示第二次补货,Jn表示第n次补货).
求SQL语句:每张原单号即不带J的单号共补白菜的次数(补单明细表中如果有白菜,就算是补白菜,否则算是
补其他货物)和补其他货物的次数,实现这些功能的代码如何写?

[liangCK]

SELECT A.单号,
    SUM(CASE WHEN C.货物名称 = '白菜' THEN 1 ELSE 0 END) AS 补白菜数,
    SUM(CASE WHEN C.货物名称 = '白菜' THEN 0 ELSE 1 END) AS 补其他货物数
FROM 主表 AS A
    JOIN 主表 AS B
ON B.单号 LIKE A.单号 + '_%'
     AND A.单号 NOT LIKE '%J%' AND LEN(A.单号) = 3
    JOIN 明细表 AS C
ON B.单号 = C.单号
GROUP BY A.单号;

[pt1314917]

--> 测试数据: 主表
if object_id('主表') is not null drop table 主表
create table 主表 (单号 varchar(5),日期 datetime)
insert into 主表
select '001','2009-01-01' union all
select '002','2009-01-02' union all
select '001J1','2009-01-03' union all
select '001J2','2009-01-03' union all
select '002J1','2009-01-03' union all
select '002J2','2009-01-08' union all
select '002J3','2009-01-09'
--> 测试数据: 明细表
if object_id('明细表') is not null drop table 明细表
create table 明细表 (单号 varchar(5),货物名称 varchar(4))
insert into 明细表
select '001','大米' union all
select '001','萝卜' union all
select '002','香菇' union all
select '002','黄豆' union all
select '001J1','面条' union all
select '001J1','白菜' union all
select '001J2','白菜' union all
select '001J2','橙' union all
select '002J1','白菜' union all
select '002J1','橙' union all
select '002J2','大米' union all
select '002J3','香菇'

select a.单号,补白菜的次数=sum(case 货物名称 when '白菜' then 1 else 0 end),
补其他菜的次数=sum(case 货物名称 when '白菜' then 0 else 1 end)
 from 主表 a,明细表 b
where b.单号 like a.单号+'j%'
group by a.单号

--结果：
单号    补白菜的次数      补其他菜的次数
----- ----------- -----------
001   2           2
002   1           3

[zsyxue]

进来学习一下

[tansx]

谢谢!,有个我之前没说清楚.
补单明细表中如果有白菜,就算是补白菜,否则算是
补其他货物,同一张单不重复计算)和补其他货物的次数,

实现这些功能的代码如何写?

[tansx]

一张补单只计算一次,有白菜的就算到补白菜次数,否则算到其他货物次数中.求代码..

[pt1314917]

--这样？

select a.单号,补白菜的次数=sum(case 货物名称 when '白菜' then 1 else 0 end),
补其他菜的次数=sum(case 货物名称 when '白菜' then 0 else 1 end)
 from 主表 a,(select distinct * from 明细表) b
where b.单号 like a.单号+'j%'
group by a.单号

[liangCK]

SELECT A.单号,
    SUM(补白菜数) AS 补白菜数,
    SUM(补其他货物数) AS 补其他货物数
FROM 主表 AS A
    LEFT JOIN (
        SELECT 单号,
            CASE WHEN EXISTS(SELECT * FROM 明细表
                             WHERE T.单号 = 单号 AND 货物名称 = '白菜')
                    THEN 1 ELSE 0 END AS 补白菜数,
            CASE WHEN NOT EXISTS(SELECT * FROM 明细表
                                 WHERE T.单号 = 单号 AND 货物名称 = '白菜')
                    THEN 1 ELSE 0 END AS 补其他货物数
        FROM 明细表 AS T
        WHERE 单号 LIKE '%J%'
        GROUP BY 单号
    ) AS B
ON A.单号 NOT LIKE '%J%' AND LEFT(B.单号,3) = A.单号
GROUP BY A.单号;

[tansx]

1.
SELECT a.ghao, 染料次数 = SUM(CASE b.leibie WHEN '染料' THEN 1 ELSE 0 END),
其他次数 = SUM(CASE b.leibie WHEN '染料' THEN 0 ELSE 1 END)
FROM mdbpeifang a,
(SELECT DISTINCT ghao, leibie
FROM mdbpeifanglist) b
WHERE b.ghao LIKE a.ghao AND a.riqi = '2010-01-25 '
GROUP BY a.ghao
--结果
091224004 0 2
100115032J1 1 2
100115065J1 0 1
100115066J1 0 2
100115066J2 1 0
100115066J3 1 0
100116025J1 1 0
100116025J2 1 0
100116035J1 1 3
100116035J2 1 0
100116035J3 1 0
100119037J1 1 0
100119037J2 0 2
100119039J2 1 0
100119039J3 1 0
100120003 1 5
100120004J1 0 2
100120004J2 0 1
100120046J1 1 0
100120046J2 0 1

2.
SELECT a.ghao, 染料次数 = SUM(CASE b.leibie WHEN '染料' THEN 1 ELSE 0 END),
其他次数 = SUM(CASE b.leibie WHEN '染料' THEN 0 ELSE 1 END)
FROM mdbpeifang a,
(SELECT DISTINCT ghao, leibie
FROM mdbpeifanglist) b
WHERE b.ghao LIKE a.ghao+'J%' AND a.riqi = '2010-01-25 '
GROUP BY a.ghao

--结果

（所影响的行数为 0 行）
为空

请问这是为什么?

[pt1314917]

WHERE b.ghao LIKE a.ghao AND a.riqi = '2010-01-25 '
---
条件错了吧。
b.ghao like a.ghao+'j%'

[tansx]

1.
SELECT a.ghao, 染料次数 = SUM(CASE b.leibie WHEN '染料' THEN 1 ELSE 0 END),
其他次数 = SUM(CASE b.leibie WHEN '染料' THEN 0 ELSE 1 END)
FROM mdbpeifang a,
(SELECT DISTINCT ghao, leibie
FROM mdbpeifanglist) b
WHERE b.ghao LIKE a.ghao AND a.riqi = '2010-01-25 '
GROUP BY a.ghao
--结果
091224004 0 2
100115032J1 1 2
100115065J1 0 1
100115066J1 0 2
100115066J2 1 0
100115066J3 1 0
100116025J1 1 0
100116025J2 1 0
100116035J1 1 3
100116035J2 1 0
100116035J3 1 0
100119037J1 1 0
100119037J2 0 2
100119039J2 1 0
100119039J3 1 0
100120003 1 5
100120004J1 0 2
100120004J2 0 1
100120046J1 1 0
100120046J2 0 1

2.
SELECT a.ghao, 染料次数 = SUM(CASE b.leibie WHEN '染料' THEN 1 ELSE 0 END),
其他次数 = SUM(CASE b.leibie WHEN '染料' THEN 0 ELSE 1 END)
FROM mdbpeifang a,
(SELECT DISTINCT ghao, leibie
FROM mdbpeifanglist) b
WHERE b.ghao LIKE a.ghao+'J%' AND a.riqi = '2010-01-25 '
GROUP BY a.ghao

--结果

（所影响的行数为 0 行）
为空

请问:
第1和第2的区别是WHERE b.ghao LIKE a.ghao与WHERE b.ghao LIKE a.ghao+'J%',所出现的结果全不相同

这是为什么?

[tansx]

如果用WHERE b.ghao LIKE a.ghao+'J%'这个条件,查询为空啊.

[pt1314917]

a和b表的数据是什么样的呢？

[tansx]

谢谢各位!
按liangCK的方法搞定.
SELECT A.单号,
SUM(补白菜数) AS 补白菜数,
SUM(补其他货物数) AS 补其他货物数
FROM 主表 AS A
LEFT JOIN (
SELECT 单号,
CASE WHEN EXISTS(SELECT * FROM 明细表
WHERE T.单号 = 单号 AND 货物名称 = '白菜')
THEN 1 ELSE 0 END AS 补白菜数,
CASE WHEN NOT EXISTS(SELECT * FROM 明细表
WHERE T.单号 = 单号 AND 货物名称 = '白菜')
THEN 1 ELSE 0 END AS 补其他货物数
FROM 明细表 AS T
WHERE 单号 LIKE '%J%'
GROUP BY 单号
) AS B
ON A.单号 NOT LIKE '%J%' AND LEFT(B.单号,3) = A.单号
GROUP BY A.单号

[tansx]

我在以下语句中加入条件(红色部分)后,查询结果为什么是空(实际上是有记录的).
SELECT A.单号,
SUM(补白菜数) AS 补白菜数,
SUM(补其他货物数) AS 补其他货物数
FROM 主表 AS A
LEFT JOIN (
SELECT 单号,
CASE WHEN EXISTS(SELECT * FROM 明细表
WHERE T.单号 = 单号 AND 货物名称 = '白菜')
THEN 1 ELSE 0 END AS 补白菜数,
CASE WHEN NOT EXISTS(SELECT * FROM 明细表
WHERE T.单号 = 单号 AND 货物名称 = '白菜')
THEN 1 ELSE 0 END AS 补其他货物数
FROM 明细表 AS T
WHERE 单号 LIKE '%J%'
GROUP BY 单号
) AS B
ON A.单号 NOT LIKE '%J%' AND LEFT(B.单号,3) = A.单号
where 补白菜数>2
GROUP BY A.单号