select a.proname,b.numname,a.proid from product a inner join pronum b on a.proid=b.proid
select a.proname,b.numname,a.proid from product a inner join pronum b on a.proid=b.pro
---测试数据--- if object_id('[product]') is not null drop table [product] go create table [product]([proid] int,[proname] varchar(5)) insert [product] select 1,'产品1' union all select 2,'产品2' if object_id('[pronum]') is not null drop table [pronum] go create table [pronum]([id] int,[numname] varchar(2),[proid] int) insert [pronum] select 1,'aa',1 union all select 2,'bb',1
---查询--- select a.proid, a.proname, b.id as pronum_id, b.numname from product a left join pronum b on a.proid=b.proid---结果--- proid proname pronum_id numname ----------- ------- ----------- ------- 1 产品1 1 aa 1 产品1 2 bb 2 产品2 NULL NULL(所影响的行数为 3 行)
---查询--- select a.proname,b.numname,a.proid from product a left join pronum b on a.proid=b.proid---结果--- proname numname proid ------- ------- ----------- 产品1 aa 1 产品1 bb 1 产品2 NULL 2(所影响的行数为 3 行)
SELECT A.*,B.* FROM product A LEFT JOIN pronum ON A.proid =B.proid
---查询--- select a.proname,min(b.numname),a.proid from product a left join pronum b on a.proid=b.proid group by a.proname,a.proid---结果--- proname proid ------- ---- ----------- 产品1 aa 1 产品2 NULL 2(所影响的行数为 2 行)警告: 聚合或其它 SET 操作消除了空值。
---测试数据--- if object_id('[product]') is not null drop table [product] go create table [product]([proid] int,[proname] varchar(5)) insert [product] select 1,'产品1' union all select 2,'产品2' if object_id('[pronum]') is not null drop table [pronum] go create table [pronum]([id] int,[numname] varchar(2),[proid] int) insert [pronum] select 1,'aa',1 union all select 2,'bb',1---查询--- select a.proname,min(b.numname),a.proid from product a left join pronum b on a.proid=b.proid group by a.proname,a.proid---结果--- proname proid ------- ---- ----------- 产品1 aa 1 产品2 NULL 2(所影响的行数为 2 行)
SELECT p.ProName,b.Braid, p.classid, p.CreateTime, n.ProNumber,n.id, p.ProImgurl, n.ProNumber,p.procontent,p.proid FROM dbo.Product p LEFT OUTER JOIN dbo.ProBrand b ON p.Braid = b.Braid LEFT OUTER JOIN dbo.pronum n ON p.Proid = n.proidwhere n.id in(select min(id) from pronum group by proid) 我这样查询出来如果产品下面没有产品规格,产品名称就查询不出来了,怎么办啊
a.proid,
a.proname,
b.id as pronum_id,
b.numname
from product a
left join pronum b
on a.proid=b.proid
1 产品1
2 产品2pronum表:id numname proid
1 aa 1
2 bb 1就是这样了要求查询出来的结果是这种的
proname numname proid
产品1 aa 1
产品1 bb 1
if object_id('[product]') is not null drop table [product]
go
create table [product]([proid] int,[proname] varchar(5))
insert [product]
select 1,'产品1' union all
select 2,'产品2'
if object_id('[pronum]') is not null drop table [pronum]
go
create table [pronum]([id] int,[numname] varchar(2),[proid] int)
insert [pronum]
select 1,'aa',1 union all
select 2,'bb',1
---查询---
select
a.proid,
a.proname,
b.id as pronum_id,
b.numname
from product a
left join pronum b
on a.proid=b.proid---结果---
proid proname pronum_id numname
----------- ------- ----------- -------
1 产品1 1 aa
1 产品1 2 bb
2 产品2 NULL NULL(所影响的行数为 3 行)
select
a.proname,b.numname,a.proid
from product a
left join pronum b
on a.proid=b.proid---结果---
proname numname proid
------- ------- -----------
产品1 aa 1
产品1 bb 1
产品2 NULL 2(所影响的行数为 3 行)
如果proname中有重复的名称 只显示一条记录呢??---结果---
proname numname proid
------- ------- -----------
产品1 aa 1
产品2 NULL 2
结果要求是这样
---查询---
select
a.proname,min(b.numname),a.proid
from product a
left join pronum b
on a.proid=b.proid
group by a.proname,a.proid---结果---
proname proid
------- ---- -----------
产品1 aa 1
产品2 NULL 2(所影响的行数为 2 行)警告: 聚合或其它 SET 操作消除了空值。
if object_id('[product]') is not null drop table [product]
go
create table [product]([proid] int,[proname] varchar(5))
insert [product]
select 1,'产品1' union all
select 2,'产品2'
if object_id('[pronum]') is not null drop table [pronum]
go
create table [pronum]([id] int,[numname] varchar(2),[proid] int)
insert [pronum]
select 1,'aa',1 union all
select 2,'bb',1---查询---
select
a.proname,min(b.numname),a.proid
from product a
left join pronum b
on a.proid=b.proid
group by a.proname,a.proid---结果---
proname proid
------- ---- -----------
产品1 aa 1
产品2 NULL 2(所影响的行数为 2 行)
p.ProImgurl, n.ProNumber,p.procontent,p.proid
FROM dbo.Product p LEFT OUTER JOIN
dbo.ProBrand b ON p.Braid = b.Braid LEFT OUTER JOIN
dbo.pronum n ON p.Proid = n.proidwhere n.id in(select min(id) from pronum group by proid)
我这样查询出来如果产品下面没有产品规格,产品名称就查询不出来了,怎么办啊