declare @cate table ([id] int)
insert @cate
select 1 union all
select 2 union all
select 3declare @detail table ([ID] int, cateID int, content char(10))
insert @detail
select 1, 1, 'xxx' union all
select 2, 1, 'sdadas' union all
select 3, 2, 'ffff' union all
select 4, 2, 'ssss' union all
select 5, 2, '545454' select a.[id],b.detailNum from @cate a,(select count(1) as detailNum, cateID from @detail group by cateid )b
where a.[id]=b.cateID-----------------------------------
上面的代码能够出来:id detailnum
1 2
2 3但是我想第一个表里有的id都列出,如:
id detailnum
1 2
2 3
3 0怎么改进?
insert @cate
select 1 union all
select 2 union all
select 3declare @detail table ([ID] int, cateID int, content char(10))
insert @detail
select 1, 1, 'xxx' union all
select 2, 1, 'sdadas' union all
select 3, 2, 'ffff' union all
select 4, 2, 'ssss' union all
select 5, 2, '545454' select a.[id],b.detailNum from @cate a,(select count(1) as detailNum, cateID from @detail group by cateid )b
where a.[id]=b.cateID-----------------------------------
上面的代码能够出来:id detailnum
1 2
2 3但是我想第一个表里有的id都列出,如:
id detailnum
1 2
2 3
3 0怎么改进?
insert @cate
select 1 union all
select 2 union all
select 3declare @detail table ([ID] int, cateID int, content char(10))
insert @detail
select 1, 1, 'xxx' union all
select 2, 1, 'sdadas' union all
select 3, 2, 'ffff' union all
select 4, 2, 'ssss' union all
select 5, 2, '545454' select a.[id],isnull(b.detailNum,0) as detailNum from @cate a,(select count(1) as detailNum, cateID from @detail group by cateid )b
where a.[id]*=b.cateIDselect a.[id],isnull(b.detailNum,0) as detailNum from @cate a left join (select count(1) as detailNum, cateID from @detail group by cateid )b
on a.[id]=b.cateID
hoho,就是用left join
(
ID INT IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(8) NULL,
TEL VARCHAR(8) NULL,
)
GO
INSERT INTO TEL
SELECT 'W','1' UNION
SELECT 'W','2'UNION
SELECT 'W','3'UNION
SELECT 'X','4'UNION
SELECT 'X','5'UNION
SELECT 'X','6'UNION
SELECT 'Y','7'UNION
SELECT 'Y','8'UNION
SELECT '衣服','春装'UNION
SELECT '衣服','冬装'UNION
SELECT 'COLOR','RED' UNION
SELECT 'COLOR','YELLOW' UNION
SELECT 'COLOR','BLACK' UNION
SELECT 'COLOR','GREEN' UNION
SELECT 'COLOR','BLACK' UNION
SELECT 'COLOR','CYAN'
GO
CREATE TABLE TELNAME
(
NAME VARCHAR(8) PRIMARY KEY,
TEL VARCHAR(8000) NULL,
)
GO
INSERT INTO TELNAME
(NAME)
SELECT NAME FROM TEL GROUP BY NAME
GO
CREATE FUNCTION V_TEL
(
@TNAME VARCHAR(8)
)
RETURNS VARCHAR(8000)
BEGIN
DECLARE @MESSAGE VARCHAR(8000)
DECLARE CURTEL CURSOR
READ_ONLY
FOR SELECT TEL FROM TEL WHERE [NAME]=@TNAME
SET @MESSAGE=''
DECLARE @NAME VARCHAR(40)
OPEN CURTEL
FETCH NEXT FROM CURTEL INTO @NAME
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
SET @MESSAGE=@MESSAGE + @NAME+','
END
FETCH NEXT FROM CURTEL INTO @NAME
END
CLOSE CURTEL
DEALLOCATE CURTEL
RETURN (LEFT(@MESSAGE,LEN(@MESSAGE)-1))
END
GOUPDATE TELNAME
SET TEL=(SELECT DBO.V_TEL([NAME]) )
GO
SELECT * FROM TEL
SELECT * FROM TELNAMEDROP TABLE TEL
DROP TABLE TELNAME
DROP FUNCTION V_TEL