对有着特定规则的表进行统计分析

[redcn2004]

有这样两个表：一个为基本信息表 BasicInfo,一个为辅助明细表 Details,业务逻辑是：产品A在BasicInfo表里保存基本信息，销售信息保存在Details里面

表 BasicInfo :
BID V_KEY(产品号) BName BColor BOther
1 11 ab Y T
2 22 cd G A
3 33 ae G b
4 23 ab Y e
5 44 cd E T

表Details:
DID BID(基本信息ID) Number（销售数量） Amount(余额) AddTIme（添加时间）
1 1 100 50 2011年4月2日
2 1 20 30 2011年4月2日
3 1 20 10 ........
4 2 110 0 ........
5 4 1000 800 ........
6 5 100 0 ........

要求：输出当天总的计算情况。表如下：

BName Bcolor TotalNumber(销售数量) SoldItems(销售次数)
ab Y 1140 4
cd G 110 1
cd E 100 1

统计销售次数的规则是：只要BName和BColor相同就算一种产品。销售次数也就是当天销售记录加起来的数字。

所以问题是：对所有的记录进行聚合统计出来？

[AcHerat]

select a.Bname,a.Bcolor,sum(b.number) as TotalNumber,count(1) as SoldItems
from BasicInfo a left join Details b on a.BID = b.BID
group by a.Bname,a.Bcolor

[AcHerat]

--带日期限制
select a.Bname,a.Bcolor,sum(b.number) as TotalNumber,
     count(1) as SoldItems,convert(varchar(10),b.AddTIme,120) as SoldDate
from BasicInfo a left join Details b on a.BID = b.BID
group by a.Bname,a.Bcolor,convert(varchar(10),b.AddTIme,120)

[Shawn]

create table #temp
(
	BID INT,
	V_KEY INT,
	BName VARCHAR(10),
	BColor VARCHAR(10),
	BOther VARCHAR(10)
)
INSERT #temp
select '1', '11', 'ab', 'Y', 'T' union all
select '2', '22', 'cd', 'G', 'A' union all
select '3', '33', 'ae', 'G', 'b' union all
select '4', '23', 'ab', 'Y', 'e' union all
select '5', '44', 'cd', 'E', 'T'

create table #temp2
(
	DID int,
	BID int,
	Number int,
	Amount decimal(10, 2),
	AddTIme datetime
)
insert #temp2
select '1', '1', '100', '50', GETDATE() union all
select '2', '1', '20', '30', GETDATE() union all
select '3', '1', '20', '10', GETDATE() union all
select '4', '2', '110', '0', GETDATE() union all
select '5', '4', '1000', '800', GETDATE() union all
select '6', '5', '100', '0', GETDATE()
go
--SQL:
SELECT 
	A.BName,
	A.Bcolor,
	TotalNumber = SUM(B.Number),
	SoldItems = COUNT(1)
from #temp a, #temp2 b
where a.BID = b.BID
group by A.BName, A.Bcolor
/*
BName	Bcolor	TotalNumber	SoldItems
ab	Y	1140	4
cd	E	100	1
cd	G	110	1
*/

[ACMAIN_CHM]

select BName,Bcolor,sum(Details.Number) as TotalNumber,count(*) as SoldItems
from BasicInfo inner join Details on BasicInfo.BID=Details.BID
group by BName,Bcolor 

这个语句的结果不符合要求吗？

[qgqch2008]

DECLARE @BasicInfo TABLE (BID INT,V_KEY VARCHAR(10),BName VARCHAR(10),BColor VARCHAR(10), BOther VARCHAR(10))
INSERT @BasicInfo
SELECT 1, '11', 'ab', 'Y', 'T' UNION ALL
SELECT 2, '22', 'cd', 'G', 'A' UNION ALL
SELECT 3, '33', 'ae', 'G', 'b' UNION ALL
SELECT 4, '23', 'ab', 'Y', 'e' UNION ALL
SELECT 5, '44', 'cd', 'E', 'T'   
DECLARE @Details TABLE (DID INT, BID INT, Number INT, Amount INT, AddTIme VARCHAR(20))
INSERT @Details
SELECT 1, 1, 100, 50, '2011年4月2日' UNION ALL
SELECT 2, 1, 20, 30, '2011年4月2日' UNION ALL
SELECT 3, 1, 20, 10, '2011年4月2日' UNION ALL
SELECT 4, 2, 110, 0, '2011年4月2日' UNION ALL
SELECT 5, 4, 1000, 800, '2011年4月2日' UNION ALL
SELECT 6, 5, 100, 0, '2011年4月2日'
SELECT  BName ,
        BColor ,
        SUM(Number) AS TotalNumber ,
        COUNT(Number) AS SoldItems
FROM    ( SELECT    b.BName ,
                    b.BID ,
                    b.BColor ,
                    d.Number
          FROM      @BasicInfo b
                    LEFT JOIN @Details d ON b.BID = d.BID
          --where d.AddTIme = getdate() 加上这句查当天，getdate函数要处理成你的格式。。
        ) a
GROUP BY BName ,
        BColor
BName      BColor     TotalNumber SoldItems
---------- ---------- ----------- -----------
cd         E          100         1
ae         G          NULL        0
cd         G          110         1
ab         Y          1140        4

[--小F--]

select
 BName,Bcolor,sum(Details.Number) as TotalNumber,count(1) as SoldItems
from
 BasicInfo inner join Details on BasicInfo.BID=Details.BID
group by
 BName,Bcolor