我要建立一个查询
但是怎么也不明白怎么写sql语句
希望大家帮帮我表1:t1
ID name d1 d2 d3
-----------------------------------------
1 n1 1.3 2.2 8.15
2 n2 5.2 4.1 9.2表2:t2
tn fn LX
-----------------------------------------
t1 d1 numeric
t1 d2 numeric
t1 d3 numeric能不能用一天sql语句显示下面的效果?
ID name d1 d2 d3 d1LX d2LX d3LX
----------------------------------------------------------------------
1 n1 1.3 2.2 8.15 numeric numeric numeric
2 n2 5.2 4.1 9.2 numeric numeric numeric
我还挺着急的,后天就要给用户汇报了,就差我这点了,急啊!!!谢谢各位高手了!!!!
但是怎么也不明白怎么写sql语句
希望大家帮帮我表1:t1
ID name d1 d2 d3
-----------------------------------------
1 n1 1.3 2.2 8.15
2 n2 5.2 4.1 9.2表2:t2
tn fn LX
-----------------------------------------
t1 d1 numeric
t1 d2 numeric
t1 d3 numeric能不能用一天sql语句显示下面的效果?
ID name d1 d2 d3 d1LX d2LX d3LX
----------------------------------------------------------------------
1 n1 1.3 2.2 8.15 numeric numeric numeric
2 n2 5.2 4.1 9.2 numeric numeric numeric
我还挺着急的,后天就要给用户汇报了,就差我这点了,急啊!!!谢谢各位高手了!!!!
?
select *,d1lx=(select lx from t2 where fn='d1') ,
d2lx=(select lx from t2 where fn='d2') ,
d3lx=(select lx from t2 where fn='d3')
from t1
d1LX = (select LX from t2 where tn = 't1' and fn = 'd1'),
d2LX = (select LX from t2 where tn = 't1' and fn = 'd2'),
d3LX = (select LX from t2 where tn = 't1' and fn = 'd3')
from t1
select *, /*上面多写了一个表别名a,去掉*/
d1LX = (select LX from t2 where tn = 't1' and fn = 'd1'),
d2LX = (select LX from t2 where tn = 't1' and fn = 'd2'),
d3LX = (select LX from t2 where tn = 't1' and fn = 'd3')
from t1
SELECT *
FROM t1, (
SELECT
d1LX = MAX(CASE fn WHEN 'd1' THEN LX END),
d2LX = MAX(CASE fn WHEN 'd2' THEN LX END),
d3LX = MAX(CASE fn WHEN 'd3' THEN LX END)
FROM t2
WHERE tn = 't1'
)B
INSERT T1 SELECT 1 ,'n1', 1.3 ,2.2, 8.15
UNION ALL SELECT 2, 'n2' ,5.2, 4.1 ,9.2CREATE TABLE t2(tn VARCHAR(10), fn VARCHAR(10) ,LX VARCHAR(10))
INSERT T2 SELect 't1', 'd1' ,'numeric'
UNION ALL SELECT 't1', 'd2' ,'numeric'
UNION ALL SELECT 't1', 'd3' ,'numeric'DECLARE @s VARCHAR(8000)
SELECT @s=''
SELECT @s=@s+',['+fn+'LX]=MAX(CASE WHEN fn=''' + fn + ''' THEN LX ELSE '''' END)' FROM t2
SELECT @s='SELECT ' + STUFF(@s,1,1,'') + ' FROM t2'
SELECT @s='SELECT a.*,b.* FROM t1 a CROSS JOIN (' + @s + ') b'
EXEC(@s)DROP TABLE T1,T2
/*
1 n1 1.30 2.20 8.15 numeric numeric numeric
2 n2 5.20 4.10 9.20 numeric numeric numeric
*/
但是怎么也不明白怎么写sql语句
希望大家帮帮我表1:t1
ID name d1 d2 d3
-----------------------------------------
1 n1 1.3 2.2 8.15
2 n2 5.2 4.1 9.2表2:t2
tn fn LX
-----------------------------------------
t1 d1 numeric
t1 d2 numeric
t1 d3 numeric能不能用一天sql语句显示下面的效果?
ID name d1 d2 d3 d1LX d2LX d3LX
----------------------------------------------------------------------
1 n1 1.3 2.2 8.15 numeric numeric numeric
2 n2 5.2 4.1 9.2 numeric numeric numeric
我还挺着急的,后天就要给用户汇报了,就差我这点了,急啊!!!谢谢各位高手了!!!!补充说明:t1是主表,t1的全部字段都显示出来条件是t2表中的tn字段表示的是t1表,
t2表中的fn字段表示的是t1表中d1、d2、d3字段的字段名称,
t2表中的LX表示的t2的fn对应的t1的字段名的那个字段的类型输出这样的内容
ID name d1 d2 d3 d1LX d2LX d3LX
我是为了在程序里显示d1字段内容的时候,
直接用rs("d1LX")就能方便的得到t1表中d1字段的类型
SELECT @s='SELECT a.*,b.* FROM t1 a CROSS JOIN (' + @s + ') b'
EXEC(@s)中间加一条print @sprint的结果就是所谓的一条语句 -_-
我要的是视图的sql语句啊
不是存储过程啊
我不会用存储过程啊
邹老大的解决方法好像是用 openrowset之类的来实现.
create view myv as select * from openrowset(...执行存储过程获得结果集) x将
DECLARE @s VARCHAR(8000)
SELECT @s=''
SELECT @s=@s+',['+fn+'LX]=MAX(CASE WHEN fn=''' + fn + ''' THEN LX ELSE '''' END)' FROM t2
SELECT @s='SELECT ' + STUFF(@s,1,1,'') + ' FROM t2'
SELECT @s='SELECT a.*,b.* FROM t1 a CROSS JOIN (' + @s + ') b'
EXEC(@s)这段改为存储过程,然后用openrowset调用即可.好像还有其它几种方法没注意看,记不清了.
create procedure dbo.MyRes
as
DECLARE @s VARCHAR(8000)
SELECT @s=''
SELECT @s=@s+',['+ZDM+'ZDLX]=MAX(CASE WHEN ZDM=''' + ZDM + ''' THEN ZDLX ELSE '''' END)' FROM FieldName
SELECT @s='SELECT ' + STUFF(@s,1,1,'') + ' FROM FieldName'
SELECT @s='SELECT a.*,b.* FROM v_GQJBQK a CROSS JOIN (' + @s + ') b'
EXEC(@s)
GO这句话应该怎么写啊?
create view myv as select * from openrowset(...执行存储过程获得结果集) x
字段太多了
FROM T1 AA,
(SELECT A.TN,A.FN,A.LX AS LX1,B.FN,B.LX AS LX2,C.FN,C.LX AS LX3 FROM
(SELECT TN,FN,LX FROM T2 WHERE TN='T1' AND FN='D1' ) A JOIN
(SELECT TN,FN,LX FROM T2 WHERE TN='T1' AND FN='D2' ) B ON A.TN=B.TN JOIN
(SELECT TN,FN,LX FROM T2 WHERE TN='T1' AND FN='D3' ) C ON A.TN=B.TN ) BB