三个表,会员表,公司表,公司所属分类表,会员表和公司表是一对一的关系,公司表和公司所属分类表是一对多的关系,当一个公司有多个分类的时候,select出来的数据有很多的公司名,我想获得一个公司所属的所有分类,公司是唯一的。以下是失败的SQL语句
SELECT Company.*,CompanyCategories.Category1,Member.UserName,Member.Tel FROM Company
LEFT JOIN Member ON Member.MemberId=Company.MemberId
LEFT JOIN CompanyCategories ON CompanyCategories.CompanyId=Company.CompanyId
ORDER BY Company.CompanyId DESC
SELECT Company.*,CompanyCategories.Category1,Member.UserName,Member.Tel FROM Company
LEFT JOIN Member ON Member.MemberId=Company.MemberId
LEFT JOIN CompanyCategories ON CompanyCategories.CompanyId=Company.CompanyId
ORDER BY Company.CompanyId DESC
解决方案 »
- 在下面代码中COLLATE SQL_Latin1_General_Cp1_CI_AS的作用是什么?
- 通过一张表某个字段更新另一张表,求SQL语句。
- 一个存储过程问题?请帮帮我!
- 请问如何把文本格式的文件导入到sql server中
- 只用一条SQL 语句完成任务? OK?
- 数据更新
- 文件组 'PRIMARY' 已满 这个问题该怎么解决?
- 这种游戏你玩过吗!
- 关联查询的问题!
- 一个表每天生成记录10万多条,如何满足1年后的几亿条记录的搜索速度?
- 求一个sql 语句
- 在局域网中,如果一个客户端更新了服务器上数据库表m中的记录后,其它客户端如何及时知道表m被更改了?服务器能自动及时发出通知信息让客户端知道吗?
returns varchar(512) as begin
declare @catagory varchar(512) set @catagory=''
select @catagory=@catagory+','+Category1
from CompanyCategories where CompanyId=@CompanyId
return right(@catagory,len(@catagory)-1)
end
go
SELECT Company.*,dbo.CompanyCategories(Company.CompanyId),Member.UserName,Member.Tel FROM Company
LEFT JOIN Member ON Member.MemberId=Company.MemberId
ORDER BY Company.CompanyId DESC
MemberId,UserName,Password,.......Company表
CompanyId,MemberId,CompanyName,......CompanyCategories表
CompanyId,Category1,Category2,Category3
SELECT
MAX(A.UserName),
MAX(A.PassWord)
MAX(B.MemberId),
MAX(B.CompanyName),
Category = MAX(C.Category1) + MAX(C.Category2) + MAX(C.Category3)
FROM Member A
INNER JOIN Company B
ON A.MemberId = B.MemberId
INNER JOIN CompanyCategories C
On B.CompanyId = C.CompanyId
GROUP BY B.CompanyId
比如
序号 货物 数量
1 A 30
2 10
3 -5
4 B 20
5 100
6 C 1
7 98
declare @t table(序号 int,货物 varchar(100),数量 int)
insert into @t
select 1, 'A' , 30 union
select 2 , 'a' , 10 union
select 3 , 'a' , -5 union
select 4 , 'B' , 20 union
select 5 , 'b' , 100 union
select 6 , 'C' , 1 union
select 7 , 'c' , 98 select 序号,case when 序号=(select min(序号) from @t where 货物=a.货物) then 货物 else '' end as 货物, 数量
from @t a
order by 序号----------------------------------------------------------
多列去重复
例如:
表A
ih hc date
1 Q1 2007-1-1
1 Q1 2007-2-1
2 Q2 2007-1-5
3 Q3 2007-3-1 表B
ih idx jg
1 1 10.2
1 2 10.3
1 3 9.8
2 1 11
2 2 10.2
3 2 12 想要的结果:
ih hc date jg
1 Q1 2007-1-1 10.2
10.3
9.8
2 Q2 2007-1-5 11
10.2
3 Q3 2007-3-1 12 如何实现?谢谢,在线等!create table A(ih varchar(10),hc varchar(10),date varchar(10))
insert into A values('1', 'Q1', '2007-01-01')
insert into A values('1', 'Q1', '2007-02-01')
insert into A values('2', 'Q2', '2007-01-05')
insert into A values('3', 'Q3', '2007-03-01')
create table B(ih varchar(10),idx int,jg decimal(18,1))
insert into B values('1', 1, 10.2)
insert into B values('1', 2, 10.3)
insert into B values('1', 3, 9.8 )
insert into B values('2', 1, 11 )
insert into B values('2', 2, 10.2)
insert into B values('3', 2, 12 )
goselect
ih = case when idx=(select min(idx) from
(
select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih
) m1
where m4.ih=m1.ih) then ih else '' end ,
hc = case when idx=(select min(idx) from
(
select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih
) m2
where m4.ih=m2.ih) then hc else '' end ,
date = case when idx=(select min(idx) from
(
select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih
) m3
where m4.ih=m3.ih) then date else '' end ,
jg
from
(
select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih
) m4drop table A,B/*
ih hc date jg
---------- ---------- ---------- --------------------
1 Q1 2007-01-01 10.2
10.3
9.8
2 Q2 2007-01-05 11.0
10.2
3 Q3 2007-03-01 12.0(所影响的行数为 6 行)
*/[code=SQL]create table A(ih int,hc varchar(10),date datetime)
insert into A values(1, 'Q1', '2007-1-1')
insert into A values(1, 'Q1', '2007-2-1')
insert into A values(2, 'Q2', '2007-1-5')
insert into A values(3, 'Q3', '2007-3-1')
create table B(ih int,idx int,jg decimal(18,1))
insert into B values(1, 1, 10.2)
insert into B values(1, 2, 10.3)
insert into B values(1, 3, 9.8 )
insert into B values(2, 1, 11 )
insert into B values(2, 2, 10.2)
insert into B values(3, 2, 12 )
goselect
ih = case when idx=(select min(idx) from
(
select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih
) m1
where m4.ih=m1.ih) then cast(ih as varchar) else '' end ,
hc = case when idx=(select min(idx) from
(
select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih
) m2
where m4.ih=m2.ih) then hc else '' end ,
date = case when idx=(select min(idx) from
(
select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih
) m3
where m4.ih=m3.ih) then convert(varchar(10),date,120) else '' end ,
jg
from
(
select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih
) m4drop table A,B/*
ih hc date jg
------------------------------ ---------- ---------- --------------------
1 Q1 2007-01-01 10.2
10.3
9.8
2 Q2 2007-01-05 11.0
10.2
3 Q3 2007-03-01 12.0(所影响的行数为 6 行)
*/[/code]
现在查询出来的结果:
CompanyId MemberId CompanyName Category1 UserName
81 115 佛山市xxx铝业有限公司 12 bjylx
81 115 佛山市xxx铝业有限公司 13 bjylx
80 114 北京xx木业有限公司 14 skmy
80 114 北京xx木业有限公司 15 skmy
80 114 北京xx木业有限公司 16 skmy
80 114 北京xx木业有限公司 17 skmy
79 113 北京xxx铁艺装饰有限公司 12 zijinge
79 113 北京xxx铁艺装饰有限公司 13 zijinge
79 113 北京xxx铁艺装饰有限公司 14 zijinge
79 113 北京xxx铁艺装饰有限公司 15 zijinge
79 113 北京xxx铁艺装饰有限公司 16 zijinge
79 113 北京xxx铁艺装饰有限公司 17 zijinge
78 112 北京xxxx工贸有限公司 11 bjyphg
78 112 北京xxxx工贸有限公司 12 bjyphg
78 112 北京xxxx工贸有限公司 13 bjyphg
78 112 北京xxxx工贸有限公司 14 bjyphg
77 111 xxxx门业 12 hbswzj我需要的结果是:
81 115 佛山市xxx铝业有限公司 12,13 bjylx
80 114 北京xx木业有限公司 14,15,16,17 skmy
79 113 北京xxx铁艺装饰有限公司 12,13,14,15,16,17 zijinge
78 112 北京xxxx工贸有限公司 11,12,13,14 bjyphg
77 111 xxxx门业 12 hbswzj
go
create table CompanyCategories (CompanyId int, MemberId int, CompanyName varchar(32), Category1 int, UserName varchar(32))
go
insert into CompanyCategories values (81, 115 ,'佛山市xxx铝业有限公司', 12, 'bjylx')
insert into CompanyCategories values (81, 115 ,'佛山市xxx铝业有限公司', 13, 'bjylx')
insert into CompanyCategories values (80, 114 ,'北京xx木业有限公司', 14, 'skmy')
insert into CompanyCategories values (80, 114 ,'北京xx木业有限公司', 15, 'skmy')
insert into CompanyCategories values (80, 114 ,'北京xx木业有限公司', 16, 'skmy')
insert into CompanyCategories values (80, 114 ,'北京xx木业有限公司', 17, 'skmy')
insert into CompanyCategories values (79, 113 ,'北京xxx铁艺装饰有限公司', 12, 'zijinge')
insert into CompanyCategories values (79, 113 ,'北京xxx铁艺装饰有限公司', 13, 'zijinge')
insert into CompanyCategories values (79, 113 ,'北京xxx铁艺装饰有限公司', 14, 'zijinge')
insert into CompanyCategories values (79, 113 ,'北京xxx铁艺装饰有限公司', 15, 'zijinge')
insert into CompanyCategories values (79, 113 ,'北京xxx铁艺装饰有限公司', 16, 'zijinge')
insert into CompanyCategories values (79, 113 ,'北京xxx铁艺装饰有限公司', 17, 'zijinge')
insert into CompanyCategories values (78, 112 ,'北京xxxx工贸有限公司', 11, 'bjyphg')
insert into CompanyCategories values (78, 112 ,'北京xxxx工贸有限公司', 12, 'bjyphg')
insert into CompanyCategories values (78, 112 ,'北京xxxx工贸有限公司', 13, 'bjyphg')
insert into CompanyCategories values (78, 112 ,'北京xxxx工贸有限公司', 14, 'bjyphg')
insert into CompanyCategories values (77, 111 ,'xxxx门业', 12, 'hbswzj')
go
create function dbo.fn_CompanyCategories ( @CompanyId int )
returns varchar(512) as begin
declare @catagory varchar(512) set @catagory=''
select @catagory=@catagory+','+cast(Category1 as varchar)
from CompanyCategories where CompanyId=@CompanyId
return right(@catagory,len(@catagory)-1)
end
go
select CompanyId , MemberId , CompanyName , Category = dbo.fn_CompanyCategories(CompanyId) , UserName
from (select distinct CompanyId , MemberId , CompanyName , UserName from CompanyCategories) as a-- CompanyId MemberId CompanyName Category UserName
-- 77 111 xxxx门业 12 hbswzj
-- 78 112 北京xxxx工贸有限公司 11,12,13,14 bjyphg
-- 79 113 北京xxx铁艺装饰有限公司 12,13,14,15,16,17 zijinge
-- 80 114 北京xx木业有限公司 14,15,16,17 skmy
-- 81 115 佛山市xxx铝业有限公司 12,13 bjylx
--
go
drop table CompanyCategories
drop function dbo.fn_CompanyCategories
go
用函数效率比语句如何?
向 substring 函数传递了无效的 length 参数。是不是企业没有分类的时候会出现上面的提示呢?