用自定义函数吧,定义好函数就可以用一个SQL查询出来.1, create function getstr(@id Nchar(4000)) returns Nvarchar(4000) as begin declare @str Nvarchar(2000) set @str=N'' select @str=@str+rtrim(dh)+N',' from (SELECT qybm, qymc, dh, fspc FROM table1 WHERE (fspc= '1') and qybm=@id UNION SELECT qybm, qymc, dh, fspc FROM table2 WHERE (fspc= '1') and qybm=@id )temp if @str<>N'' set @str=left(@str,len(@str)-1) return @str endGO 2, select qybm,dbo. getstr( qybm) from (SELECT qybm, qymc, dh, fspc FROM table1 WHERE (fspc= '1') UNION SELECT qybm, qymc, dh, fspc FROM table2 WHERE (fspc= '1'))temp group by qybm
SELECT a.qybm, a.qymc, a.dh+' '+b.dh, a.fspc FROM table1 a,table2 b WHERE a.fspc= '1' and b.fspc='1' and a.qybh in (select distinct qybh from table2 where fspc='1') UNION SELECT a.qybm, a.qymc, a.dh, a.fspc FROM table1 a WHERE a.fspc= '1' and a.qybh not in (select distinct qybh from table2 where fspc='1') UNION SELECT a.qybm, a.qymc, a.dh, a.fspc FROM table2 b WHERE b.fspc= '1' and b.qybh not in (select distinct qybh from table1 where fspc='1')
SELECT a.qybm, a.qymc, a.dh+' '+b.dh, a.fspc FROM table1 a,table2 b WHERE a.fspc= '1' and b.fspc='1' and a.qybm in (select distinct qybm from table2 where fspc='1') and a.qybm=b.qybm UNION SELECT a.qybm, a.qymc, a.dh, a.fspc FROM table1 a WHERE a.fspc= '1' and a.qybm not in (select distinct qybm from table2 where fspc='1') UNION SELECT b.qybm, b.qymc, b.dh, b.fspc FROM table2 b WHERE b.fspc= '1' and b.qybm not in (select distinct qybm from table1 where fspc='1')
用临时表和游标做吧。
create function getstr(@id Nchar(4000))
returns Nvarchar(4000)
as
begin
declare @str Nvarchar(2000)
set @str=N''
select @str=@str+rtrim(dh)+N',' from
(SELECT qybm, qymc, dh, fspc
FROM table1
WHERE (fspc= '1') and qybm=@id
UNION
SELECT qybm, qymc, dh, fspc
FROM table2
WHERE (fspc= '1') and qybm=@id
)temp
if @str<>N''
set @str=left(@str,len(@str)-1)
return @str
endGO
2,
select qybm,dbo. getstr( qybm) from
(SELECT qybm, qymc, dh, fspc
FROM table1
WHERE (fspc= '1')
UNION
SELECT qybm, qymc, dh, fspc
FROM table2
WHERE (fspc= '1'))temp
group by qybm
FROM table1 a,table2 b
WHERE a.fspc= '1'
and b.fspc='1'
and a.qybh in (select distinct qybh from table2 where fspc='1')
UNION
SELECT a.qybm, a.qymc, a.dh, a.fspc
FROM table1 a
WHERE a.fspc= '1'
and a.qybh not in (select distinct qybh from table2 where fspc='1')
UNION
SELECT a.qybm, a.qymc, a.dh, a.fspc
FROM table2 b
WHERE b.fspc= '1'
and b.qybh not in (select distinct qybh from table1 where fspc='1')
qybm qymc fspc
12 21 456546456 1
1a 1aa 9889 111 1 <<
1a 1aa 9889 222 1 <<
1a 1aa 9889 456546456 1 << 不对
2a 2aa 222 1
4a 4aa 5656 1
FROM table1 a,table2 b
WHERE a.fspc= '1'
and b.fspc='1'
and a.qybm in (select distinct qybm from table2 where fspc='1')
and a.qybm=b.qybm
UNION
SELECT a.qybm, a.qymc, a.dh, a.fspc
FROM table1 a
WHERE a.fspc= '1'
and a.qybm not in (select distinct qybm from table2 where fspc='1')
UNION
SELECT b.qybm, b.qymc, b.dh, b.fspc
FROM table2 b
WHERE b.fspc= '1'
and b.qybm not in (select distinct qybm from table1 where fspc='1')
测试过定义函数就可以在增加单位的情况下,不用修改函数了吗?
你的函数是怎么写的?
蚂蚁大哥的函数我看到了,很不错,但是在增加单位的时候也需要修改代码
zjcxc(邹建) 你的方法是什么?