各位高手在下有一个问题请教大家。
具体问题如下:
我又两个表:BrandDetails,BrandJoinBrandDetailsId BrandDetails BrandListId BrandTime
1 卡西欧 FH20 1 2009-8-11 11:56:092 三星 ES55 1 2009-8-11 11:56:093 索尼 T77 1 2009-8-11 11:56:094 佳能 450D 1 2009-8-11 11:56:09 5 富士 S1500 1 2009-8-11 11:56:096 松下 LX3 1 2009-8-11 11:56:09Id memberid BrandListId BrandDetailsId
1 111 1 1
2 222 1 2
3 333 1 1
4 444 1 1以上是两个表,我想得到这样的结果Id BrandDetails BrandListId BrandTime Count
1 卡西欧 FH20 1 2009-8-11 11:56:09 32 三星 ES55 1 2009-8-11 11:56:09 13 索尼 T77 1 2009-8-11 11:56:09 04 佳能 450D 1 2009-8-11 11:56:09 0 5 富士 S1500 1 2009-8-11 11:56:09 06 松下 LX3 1 2009-8-11 11:56:09 0
不知道想得到这样的结果怎样写SQL,请各位大侠告诉我一下,在下学习了。万分感谢了!
具体问题如下:
我又两个表:BrandDetails,BrandJoinBrandDetailsId BrandDetails BrandListId BrandTime
1 卡西欧 FH20 1 2009-8-11 11:56:092 三星 ES55 1 2009-8-11 11:56:093 索尼 T77 1 2009-8-11 11:56:094 佳能 450D 1 2009-8-11 11:56:09 5 富士 S1500 1 2009-8-11 11:56:096 松下 LX3 1 2009-8-11 11:56:09Id memberid BrandListId BrandDetailsId
1 111 1 1
2 222 1 2
3 333 1 1
4 444 1 1以上是两个表,我想得到这样的结果Id BrandDetails BrandListId BrandTime Count
1 卡西欧 FH20 1 2009-8-11 11:56:09 32 三星 ES55 1 2009-8-11 11:56:09 13 索尼 T77 1 2009-8-11 11:56:09 04 佳能 450D 1 2009-8-11 11:56:09 0 5 富士 S1500 1 2009-8-11 11:56:09 06 松下 LX3 1 2009-8-11 11:56:09 0
不知道想得到这样的结果怎样写SQL,请各位大侠告诉我一下,在下学习了。万分感谢了!
FROM BrandDetails A,BrandJoin B
WHERE B.BrandListId =A. BrandListId
AND B.BrandDetailsId =A.ID
,(SELECT COUNT(1) FROM BrandJoin WHERE ID=BrandDetails.ID ) 'COUNT'
FROM BrandDetails
[count]=ISNULL((SELECT COUNT(*) FROM b WHERE A.ID=B.BrandListId),0)
from A
A.*,
ISNULL(B.[count],0) AS [count]
FROM BrandDetails AS A
LEFT JOIN (
SELECT BrandDetailsId,BrandListId,COUNT(*) AS [count]
FROM BrandJoin
GROUP BY BrandDetailsId,BrandListId
) AS B
ON A.Id = B.BrandDetailsId
AND A.BrandListId = B.BrandListId
[count]=ISNULL((SELECT COUNT(*) FROM BrandJoin WHERE A.ID=B.BrandListId),0)
from BrandDetails A
[count]=ISNULL((SELECT COUNT(*) FROM BrandJoin WHERE A.ID=BrandListId),0)
from BrandDetails A
[count]=ISNULL((SELECT COUNT(*) FROM BrandJoin WHERE A.ID=BrandDetailsId ),0)
from BrandDetails A
A.Id ,A.BrandDetails, A.BrandListId,A.BrandTime
union
(
select BrandDetailsId ,count(1) mycount from BrandJoin group by BrandDetailsId
) a on a.BrandDetailsId = b.id
group by a.Id ,a.BrandDetails, a.BrandListId , a.BrandTime
inner join
(
select BrandDetailsId ,count(1) mycount from BrandJoin group by BrandDetailsId
) a on a.BrandDetailsId = b.id
FROM BrandDetails A left join BrandJoin B
on B.BrandListId =A. BrandListId
AND B.BrandDetailsId =A.ID
group by A.Id,a.BrandDetails,a.BrandListId,a.BrandTime
FROM BrandDetails A left join BrandJoin B
on B.BrandListId =A. BrandListId
AND B.BrandDetailsId =A.ID
group by A.Id,a.BrandDetails,a.BrandListId,a.BrandTime
select a.Id,BrandDetails,BrandListId,BrandTime,Count(b.id)
from BrandDetails a left join BrandJoin b
on a.id=b.BrandDetailsId
group by a.Id,BrandDetails,BrandListId,BrandTime
insert into ta select 1, '卡西欧 FH20', 1 , '2009-8-11 11:56:09' union all select 2, '三星 ES55', 1, '2009-8-11 11:56:09' union all select 3 , '索尼 T77', 1, '2009-8-11 11:56:09' union all select 4, '佳能 450D', 1, '2009-8-11 11:56:09' union all select 5, '富士 S1500', 1, '2009-8-11 11:56:09' union all select 6, '松下 LX3', 1, '2009-8-11 11:56:09' create table tb (Id int, memberid int, BrandListId int, BrandDetailsId int)
insert into tb select 1 , 111, 1 , 1
union all select 2, 222, 1, 2
union all select 3, 333, 1, 1
union all select 4, 444, 1, 1
select a.* ,isnull(b.[count],0) from ta as a
left join
(select BrandListId, BrandDetailsId,count(BrandDetailsId)as [count]
from tb
group by BrandListId, BrandDetailsId ) as b
on a.id=b.BrandListId
/*
Id BrandDetails BrandListId BrandTime
----------- -------------------- ----------- ----------------------- -----------
1 卡西欧 FH20 1 2009-08-11 11:56:09.000 3
1 卡西欧 FH20 1 2009-08-11 11:56:09.000 1
2 三星 ES55 1 2009-08-11 11:56:09.000 0
3 索尼 T77 1 2009-08-11 11:56:09.000 0
4 佳能 450D 1 2009-08-11 11:56:09.000 0
5 富士 S1500 1 2009-08-11 11:56:09.000 0
6 松下 LX3 1 2009-08-11 11:56:09.000 0(7 行受影响)
*/
insert into ta select 1, '卡西欧 FH20', 1 , '2009-8-11 11:56:09' union all select 2, '三星 ES55', 1, '2009-8-11 11:56:09' union all select 3 , '索尼 T77', 1, '2009-8-11 11:56:09' union all select 4, '佳能 450D', 1, '2009-8-11 11:56:09' union all select 5, '富士 S1500', 1, '2009-8-11 11:56:09' union all select 6, '松下 LX3', 1, '2009-8-11 11:56:09' create table tb (Id int, memberid int, BrandListId int, BrandDetailsId int)
insert into tb select 1 , 111, 1 , 1
union all select 2, 222, 1, 2
union all select 3, 333, 1, 1
union all select 4, 444, 1, 1
select a.* ,isnull(b.[count],0) from ta as a
left join
(select BrandListId, BrandDetailsId,count(BrandDetailsId)as [count]
from tb
group by BrandListId, BrandDetailsId ) as b
on a.id=b.BrandDetailsId
/*
Id BrandDetails BrandListId BrandTime
----------- -------------------- ----------- ----------------------- -----------
1 卡西欧 FH20 1 2009-08-11 11:56:09.000 3
2 三星 ES55 1 2009-08-11 11:56:09.000 1
3 索尼 T77 1 2009-08-11 11:56:09.000 0
4 佳能 450D 1 2009-08-11 11:56:09.000 0
5 富士 S1500 1 2009-08-11 11:56:09.000 0
6 松下 LX3 1 2009-08-11 11:56:09.000 0(6 行受影响)
*/
left join
(select BrandListId, BrandDetailsId,count(BrandDetailsId) [count]
from tb
group by BrandListId, BrandDetailsId ) b
on a.id=b.BrandDetailsId