insert into text0(text1,text2)
select (SELECT jos_sobi2_fields_data.data_txt
FROM jos_sobi2_fields_data
WHERE jos_sobi2_fields_data.fieldid =1),(SELECT jos_sobi2_fields_data.data_txt
FROM jos_sobi2_fields_data
WHERE jos_sobi2_fields_data.fieldid =2)为什么会提示Subquery returns more than 1 row
正确的写法应该是什么?
select (SELECT top 1 data_txt FROM jos_sobi2_fields_data WHERE fieldid =1),
(SELECT top 1 data_txt FROM jos_sobi2_fields_data WHERE fieldid =2) 返回行数大于0
insert into text0(text1,text2)
select
(
SELECT TOP 1 jos_sobi2_fields_data.data_txt
FROM jos_sobi2_fields_data
WHERE jos_sobi2_fields_data.fieldid =1
),
(
SELECT TOP 1 jos_sobi2_fields_data.data_txt
FROM jos_sobi2_fields_data
WHERE jos_sobi2_fields_data.fieldid =2
)
FROM jos_sobi2_fields_data
WHERE jos_sobi2_fields_data.fieldid =1SELECT jos_sobi2_fields_data.data_txt
FROM jos_sobi2_fields_data
WHERE jos_sobi2_fields_data.fieldid =2这两个语句至少有一个其返回结果不止一行.
另一个列用UPDATE来更新
tb
id v
1 a
1 b
2 d
2 e
2 f这涉及到两两组合成一行后的组合问题。 楼主问的并不明确,取 top 1 只是拼凑。
1 gulou
2 350001
3 fuzhou
4 fuzhou
5 gulou
6 china
1 gulou
2 350001
3 fuzhou
4 fuzhou
5 gulou
6 china
要得到的结果 是 gulou 350001 fuzhou fuzhou gulou china
gulou 350001 fuzhou fuzhou gulou china
还有问下楼上的 update 能使用SELECT 语句吗?怎么用?
--> 测试数据: @s
declare @s table (fieldid int,data_txt varchar(6))
insert into @s
select 1,'gulou' union all
select 2,'350001' union all
select 3,'fuzhou' union all
select 4,'fuzhou' union all
select 5,'gulou' union all
select 6,'china' union all
select 1,'gulou' union all
select 2,'350001' union all
select 3,'fuzhou' union all
select 4,'fuzhou' union all
select 5,'gulou' union all
select 6,'china'
select id=identity(int,1,1),* into # from @s aselect
max(case fieldid when 1 then data_txt else '' end),
max(case fieldid when 2 then data_txt else '' end),
max(case fieldid when 3 then data_txt else '' end),
max(case fieldid when 4 then data_txt else '' end),
max(case fieldid when 5 then data_txt else '' end),
max(case fieldid when 6 then data_txt else '' end)
from (select px=(select count(1) from # where fieldid=a.fieldid and id<a.id),fieldid,data_txt from # a)a
group by px