有两个表:
A:(主表)
aid bid
--------------
1 1
2 2
3 2
3 1
3 1
B:(外表)
bid bname
---------------
1 b1
2 b2
求结果(视图),即统计每一条aid 对应的 b1 b2的记录个数:aid bid bidcount
1 1 1
1 2 0
2 1 0
2 2 1
3 1 2
3 2 1
A:(主表)
aid bid
--------------
1 1
2 2
3 2
3 1
3 1
B:(外表)
bid bname
---------------
1 b1
2 b2
求结果(视图),即统计每一条aid 对应的 b1 b2的记录个数:aid bid bidcount
1 1 1
1 2 0
2 1 0
2 2 1
3 1 2
3 2 1
-- Author: liangCK 小梁
-- Date : 2008-11-26 11:59:33
---------------------------------
--> 生成测试数据: @tb1
DECLARE @tb1 TABLE (aid INT,bid INT)
INSERT INTO @tb1
SELECT 1,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,2 UNION ALL
SELECT 3,1 UNION ALL
SELECT 3,1
--> 生成测试数据: @tb2
DECLARE @tb2 TABLE (bid INT,bname VARCHAR(2))
INSERT INTO @tb2
SELECT 1,'b1' UNION ALL
SELECT 2,'b2'--SQL查询如下:SELECT
A.aid,
B.bid,
COUNT(C.aid) AS bidcount
FROM(
SELECT DISTINCT aid
FROM @tb1
) AS A
CROSS JOIN @tb2 AS B
LEFT JOIN @tb1 AS C
ON A.aid=C.aid
AND B.bid=C.bid
GROUP BY A.aid,B.bid/*
aid bid bidcount
----------- ----------- -----------
1 1 1
1 2 0
2 1 0
2 2 1
3 1 2
3 2 1(6 行受影响)*/
declare @a table(aid int,bid int)
insert into @a select 1,1 union all
select 2,2 union all
select 3,3 union all
select 3,1 union all
select 3,1declare @b table(bid int,bname varchar(10))
insert into @b select 1,'b1' union all
select 2,'b2'
select b.aid,b.bid,'bidcount'=isnull(bidcount,0) from
(select * from (select distinct aid from @a)a left join @b on 1=1)b
left join
(select aid,bid,count(*) as bidcount from @a group by aid,bid)c
on b.aid=c.aid and b.bid=c.bid
CROSS JOIN 学习到了:)是说怎么不加ON总报错.