这样的SQL语句如何写
t1结构
id int
name varchar(20)
t2结构(表t2和t1的关系:t1.id = t2.cid)
id int
cid int
method varchar(20)
简单数据
t1表中数据
1 '汽车 '
2 '飞机 '
3 '船 '
t2表中数据
1 1 '外国购买 '
2 1 '国内购买 '
3 2 '外国购买 '
4 3 '外国购买 '
5 3 '国内购买 '
现在想实现的查询结果是:找出表t1中的所有商品的购买方式
结果:
1 '汽车 ' '外国购买,国内购买 '
2 '飞机 ' '外国购买 '
3 '船 ' '外国购买,国内购买 '
大家帮一下忙,谢谢了
[解决办法]
一个sql语句写不出来，需要写个合并的函数才可以，类似的帖子有很多
[解决办法]
select * from t1 a , t2 b where a.id = b.cid
[解决办法]
不要局限于一个SQL文里.
否则会很难的.
即使写出来,效率也很低的.
[解决办法]
create table t
(id smallint,name varchar(100))
insert into t
select 1, '外國購買 ' union all
select 1, '國內購買 ' union all
select 2, '外國購買 ' union all
select 3, '外國購買 ' union all
select 3, '國內購買 '
create table t1
(id smallint,name1 varchar(10))
insert into t1
select 1, '汽車 ' union all
select 2, '飛机 ' union all
select 3, '船 '
select a.*,c.name from t1 a
left join
(
select a.id,min(a.name+ ' , '+ b.name)as name
from t a join t b on a.id=b.id
where a.name <> b.name
group by a.id
union
select id,min(name) from t
group by id
having count(*)=1
)c on a.id=c.id
id name1 name
------ ---------- ----------------------------
1 汽車 外國購買 ,國內購買
2 飛机 外國購買
3 船 外國購買 ,國內購買
(3 row(s) affected)
[解决办法]
create table t
(id smallint,name varchar(100))
insert into t
select 1, '外國購買 ' union all
select 1, '國內購買 ' union all
select 2, '外國購買 ' union all
select 3, '外國購買 ' union all
select 3, '國內購買 '
create table t1
(id smallint,name1 varchar(10))
insert into t1
select 1, '汽車 ' union all
select 2, '飛机 ' union all
select 3, '船 '
select t1.*,c.name from t1,
(select a.id ,min(a.name+ ', '+b.name) as name from t a,t b
where a.id=b.id and a.name <> b.name
group by a.id
union all
select id,min(name)as name from t
group by id
having count(*)=1)c
where t1.id=c.id
order by c.id
drop table t,t1
