求一个SQL的写法

[sffshh]

表1
ID Name Date
1 AAAA 2009-10-1
2 BBBB 2009-10-2

表2
ID PID Product
1 1 商品1
2 1 商品2
3 2 商品3

结果
ID Name Date Text
1 AAAA 2009-10-1 商品1等2件商品
2 BBBB 2009-10-2 商品3等1件商品

这两个表的联合查询该怎么写？

[siegebaoniu]

4楼和7楼应该正确

[fpzgm]

表达不清。。

[jwdream2008]

Text字段从何而来？

[SQL2088]

--> 测试数据: #ta
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta (ID int,Name varchar(4),Date datetime)
insert into #ta
select 1,'AAAA','2009-10-1' union all
select 2,'BBBB','2009-10-2'
--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (ID int,PID int,Product varchar(5))
insert into #tb
select 1,1,'商品1' union all
select 2,1,'商品2' union all
select 3,2,'商品3'

select a.*,test='商品'+ltrim(a.id)+'等'+ltrim(count(*))+'件商品' 
from #ta a,#tb b
group by a.id,a.name,a.date
 
ID          Name Date                    test
----------- ---- ----------------------- ------------------------------------
1           AAAA 2009-10-01 00:00:00.000 商品1等3件商品
2           BBBB 2009-10-02 00:00:00.000 商品2等3件商品

(2 行受影响)

[SQLCenter]

--> 测试数据：#1
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1(ID int, Name varchar(8), Date datetime)
insert into #1
select 1, 'AAAA', '2009-10-1' union all
select 2, 'BBBB', '2009-10-2'
--> 测试数据：#2
if object_id('tempdb.dbo.#2') is not null drop table #2
create table #2(ID int, PID int, Product varchar(8))
insert into #2
select 1, 1, '商品1' union all
select 2, 1, '商品2' union all
select 3, 2, '商品3'

select a.*, b.Text from #1 a,
(select PID, min(Product)+'等'+ltrim(count(1))+'件商品' Text from #2 group by PID) b
where a.ID=b.PID

/*
ID          Name     Date                    Text
----------- -------- ----------------------- ----------------------------
1           AAAA     2009-10-01 00:00:00.000 商品1等2件商品
2           BBBB     2009-10-02 00:00:00.000 商品3等1件商品
*/

[SQL2088]

select a.*,test='商品'+ltrim(a.id)+'等'+ltrim(count(*))+'件商品' 
from #ta a,#tb b
where a.id=b.pid		-- 1楼漏了个条件
group by a.id,a.name,a.date
 
ID          Name Date                    test
----------- ---- ----------------------- ------------------------------------
1           AAAA 2009-10-01 00:00:00.000 商品1等2件商品
2           BBBB 2009-10-02 00:00:00.000 商品2等1件商品

(2 行受影响)

[kevn]

select *,text = (select max(product)+'等'+count(*)+'件商品' from 表2 where pid = 表1.id) from 表1

[kevn]

select *,text = (select max(product)+'等'+count(*)+'件商品' from 表2 where pid = 表1.id group by pid) from 表1

[sffshh]

[Quote=引用 5 楼 sql2088 的回复:]

SQL code
select a.*,test='商品'+ltrim(a.id)+'等'+ltrim(count(*))+'件商品'
from #ta a,#tb b
where a.id=b.pid -- 1楼漏了个条件
group by a.id,a.name,a.date

ID Name Date test
-……
[/Quote]

test='商品'+ltrim(a.id)+' 这里我想显示的是表2的第一件商品啊
比如“衣服等2件商品”

[sffshh]

不是ID值