A表
属性:ID,NAMEB表
属性:ID,NUM1,NUM2,NUM3A表一条数据对应多条B表数据要求一条SQL输出:ID,NAME,NUM1大于NUM2且NUM3不等于1的记录数,NUM1小于NUM2且NUM3不等于1的记录数,NUM3等于1的记录数跪求
属性:ID,NAMEB表
属性:ID,NUM1,NUM2,NUM3A表一条数据对应多条B表数据要求一条SQL输出:ID,NAME,NUM1大于NUM2且NUM3不等于1的记录数,NUM1小于NUM2且NUM3不等于1的记录数,NUM3等于1的记录数跪求
count1 = sum(case when a.NUM1 > a.NUM2 and a.NUM3 <> 1 then 1 else 0 end),
count2 = sum(case when a.NUM1 < a.NUM2 and a.NUM3 <> 1 then 1 else 0 end),
count3 = sum(case when a.NUM3 = 1 then 1 else 0 end)
FROM tableB as a INNER JOIN tableA as b on a.ID = b.ID
GROUP BY a.ID,b.Name
(NUM1>NUM2 and NUM3<>1) OR
(NUM1<NUM2 and NUM3<>1)
我初学,请高手们指教!
看来初学还是多看的好
写的乱七八糟,不好意思
连题目都看错了~~
A.NAME,
'NUM1大于NUM2且NUM3不等于1的记录数' = SUM(CASE WHEN NUM1 > NUM2 AND NUM <> 1 THEN 1 ELSE 0 END),
'NUM1小于NUM2且NUM3不等于1的记录数'= SUM(CASE WHEN NUM1 < NUM2 AND NUM3 <> 1 THEN 1 ELSE 0 END),
'NUM3等于1的记录数' = SUM(CASE WHEN NUM3 = 1 THEN 1 ELSE 0 END)
FROM tblA A INNER JOIN tblB B ON tblA.ID = tblB.ID
GROUP BY A.ID, A.NAME
ID int,
Name varchar(10))insert #tbA select 1,'黄'
union all select 2,'李'create table #tbB(
ID int,
Num1 int,
Num2 int,
Num3 int)
insert #tbB select 1,2,3,4
union all select 1,3,2,1
union all select 1,4,2,2
union all select 2,3,2,4
union all select 2,3,4,1select * from #tbA
select * from #tbBselect a.ID,Name,
NUM1大于NUM2且NUM3不等于1的记录数=(select count(*) from #tbB where #tbB.ID=a.ID and Num1>Num2 and Num3<>1),
NUM1小于NUM2且NUM3不等于1的记录数=(select count(*) from #tbB where #tbB.ID=a.ID and Num1<Num2 and Num3<>1),
NUM3等于1的记录数=(select count(*) from #tbB where #tbB.ID=a.ID and Num3=1)
from #tbA a
ID Name NUM1大于NUM2且NUM3不等于1的记录数 NUM1小于NUM2且NUM3不等于1的记录数 NUM3等于1的记录数
----------- ---------- ----------------------- ----------------------- -----------
1 黄 1 1 1
2 李 1 0 1(所影响的行数为 2 行)
---------------------------------------------------------
太复杂了 hellowork(一两清风) :
SELECT a.ID,b.Name,
count1 = sum(case when a.NUM1 > a.NUM2 and a.NUM3 <> 1 then 1 else 0 end),
count2 = sum(case when a.NUM1 < a.NUM2 and a.NUM3 <> 1 then 1 else 0 end),
count3 = sum(case when a.NUM3 = 1 then 1 else 0 end)
FROM tableB as a INNER JOIN tableA as b on a.ID = b.ID
GROUP BY a.ID,b.Name
---------------------------------------------------------------------------
简单明了
count1 = sum(case when isnull(a.NUM1,0) > isnull(a.NUM2,0) and isnull(a.NUM3,0) <> 1 then 1 else 0 end),
count2 = sum(case when isnull(a.NUM1,0) < isnull(a.NUM2,0) and isnull(a.NUM3,0) <> 1 then 1 else 0 end),
count3 = sum(case when isnull(a.NUM3,0) = 1 then 1 else 0 end)
FROM tableA as b Left JOIN tableB as a on a.ID = b.ID
GROUP BY a.ID,b.Name这样可以避免 tableA有的值在TableB中由于没有记录 而漏统计