异或处理问题

[cnming]

有一个表
CREATE TABLE [dbo].[T_Common_CompanyType](
[CompanyType] [int] NOT NULL,
[CompanyTypeName] [varchar](32) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [T_Common_CompanyType_PK] PRIMARY KEY CLUSTERED
(
[CompanyType] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

其中的数据如下

CompanyType, CompanyTypeName
1 船舶代理公司
2 箱公司
4 货代

其中CompanyType是按位异或方式产生的，也就是这个表中最多允许有32个独立的公司类型

我想产生这样的一个查询结果

1 船舶代理公司
2 箱公司
3 船舶代理公司，箱公司
4 货代
5 船舶代理公司，货代
6 箱公司，货代
7 船舶代理公司，箱公司，货代

请问该如何写比较有效简单的SQL查询语句？

[林虎]

------------------------------------
-- Author: happyflystone  
-- Version:V1.001  
-- Date:2008-09-12 16:55:19
------------------------------------

-- Test Data: tb
If object_id('tb') is not null 
    Drop table tb
Go
Create table tb(CompanyType int,CompanyTypeName nvarchar(6))
Go
Insert into tb
select 1,'船舶代理公司' union all
select 2,'箱公司' union all
select 4,'货代' 
Go
--Start
Select case when a.CompanyTypeName = b.CompanyTypeName  then a.CompanyType else a.CompanyType + b.CompanyType end,
case when a.CompanyTypeName = b.CompanyTypeName then a.CompanyTypeName else
a.CompanyTypeName+','+b.CompanyTypeName end
from tb a cross join tb b
where a.CompanyTypeName >= b.CompanyTypeName
order by 1


/*

----------- ------------- 
1           船舶代理公司
2           箱公司
3           箱公司,船舶代理公司
4           货代
5           货代,船舶代理公司
6           箱公司,货代

（所影响的行数为 6 行）

*/


--Result:
/*


*/
--End 

[CN_SQL]

能否对你的结果,再多描述一下其实现的规则?

[ling1874]

大家来斗地主

[林虎]

-- Test Data: tb
If object_id('tb') is not null 
    Drop table tb
Go
Create table tb(CompanyType int,CompanyTypeName nvarchar(6))
Go
Insert into tb
select 1,'船舶代理公司' union all
select 2,'箱公司' union all
select 4,'货代' 
Go
--Start
create function f_count()
returns @t table(id int,col varchar(30))
as
begin
	declare @i int
    select @i= sum(CompanyType) from tb
    declare @s varchar(30)
   
    select @s = isnull(@s+',','')+CompanyTypeName from tb
    insert into @t select @i,@s
    return
end
go

Select case when a.CompanyTypeName = b.CompanyTypeName  then a.CompanyType else a.CompanyType + b.CompanyType end,
case when a.CompanyTypeName = b.CompanyTypeName then a.CompanyTypeName else
a.CompanyTypeName+','+b.CompanyTypeName end
from tb a cross join tb b
where a.CompanyTypeName >= b.CompanyTypeName
union
select * from f_count()
order by 1
drop function f_count

/*

                                           
----------- ------------------------------ 
1           船舶代理公司
2           箱公司
3           箱公司,船舶代理公司
4           货代
5           货代,船舶代理公司
6           箱公司,货代
7           船舶代理公司,箱公司,货代

（所影响的行数为 7 行）

*/


--Result:
/*


*/
--End 

[Garnett_KG]

CREATE FUNCTION GetStr(@ID INT)
RETURNS  NVARCHAR(4000)
AS
BEGIN
	DECLARE @str NVARCHAR(4000)
	SET @str=''
	SELECT @str=@str+','+CompanyTypeName FROM T_Common_CompanyType 
	WHERE CompanyType & @ID >0
	SET @str=STUFF(@str,1,1,'')
	RETURN (@str)
END
GO


DECLARE @MaxType INT
SET @MaxType=(SELECT SUM(CompanyType) FROM T_Common_CompanyType)
SET ROWCOUNT @MaxType

SELECT IDENTITY(int,1,1) as ID INTO #Rows FROM sysobjects


--返回结果
SELECT *,dbo.GetStr(ID) as NAMES FROM #Rows
DROP TABLE #Rows
 

[cnming]

多谢stone_hu

[cnming]

多谢Garnett_KG

[anovice]

[Quote=引用 4 楼 stone_hu 的回复:]
SQL code
-- Test Data: tb
If object_id('tb') is not null
Drop table tb
Go
Create table tb(CompanyType int,CompanyTypeName nvarchar(6))
Go
Insert into tb
select 1,'船舶代理公司' union all
select 2,'箱公司' union all
select 4,'货代'
Go
--Start
create function f_count()
returns @t table(id int,col varchar(30))
as
begin
declare @i int
select @i= sum(CompanyType) from …
[/Quote]
貌似支持3行数据

[tianhuo_soft]

楼主也在为物流公司 做服务

还是在物流公司做IT？

[jixiaojie]

Create table tb(CompanyType int,CompanyTypeName nvarchar(6))
go
Insert into tb
select 1,'船舶代理公司' union all
select 2,'箱公司' union all
select 4,'货代'
go

select top 63 identity(int,1,1) as id into #1 from systypes a , systypes b

select
id,
replace
(
ltrim
(
isnull((select ' '+CompanyTypeName from tb where companytype & a.id = companytype and companytype =1),'')+
isnull((select ' '+CompanyTypeName from tb where companytype & a.id = companytype and companytype =2),'')+
isnull((select ' '+CompanyTypeName from tb where companytype & a.id = companytype and companytype =4),'')+
isnull((select ' '+CompanyTypeName from tb where companytype & a.id = companytype and companytype =8),'')+
isnull((select ' '+CompanyTypeName from tb where companytype & a.id = companytype and companytype =16),'')+
isnull((select ' '+CompanyTypeName from tb where companytype & a.id = companytype and companytype =32),'')
-- .
-- .
-- .

)
,' '
,'，'
)
from #1 a
where id <=(select sum(CompanyType) from tb)

go

drop table tb,#1

[dobear_0922]

Create table tb(CompanyType int,CompanyTypeName nvarchar(6))
Go
Insert into tb
select 1,'船舶代理公司' union all
select 2,'箱公司' union all
select 4,'货代' 
Go

CREATE FUNCTION GetStr(@ID INT)
RETURNS  NVARCHAR(100)
AS
BEGIN
    DECLARE @str NVARCHAR(100)
    SET @str=''
    SELECT @str=@str+','+CompanyTypeName FROM tb 
    WHERE CompanyType & @ID = CompanyType
    SET @str=STUFF(@str,1,1,'')
    RETURN (@str)
END
GO


DECLARE @MaxType INT
SET @MaxType=(SELECT SUM(CompanyType) FROM tb)

SELECT top(@MaxType) IDENTITY(int,1,1) as ID INTO #Rows FROM sysobjects

--返回结果
SELECT *,dbo.GetStr(ID) as NAMES FROM #Rows

/*
(7 行受影响)
ID          NAMES
----------- ----------------------------------------------------------------------------------------------------
1           船舶代理公司
2           箱公司
3           船舶代理公司,箱公司
4           货代
5           船舶代理公司,货代
6           箱公司,货代
7           船舶代理公司,箱公司,货代

(7 行受影响)
*/

drop function dbo.GetStr
DROP TABLE tb,#Rows

[durkingzhang]

CREATE FUNCTION GetStr(@ID INT)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @str NVARCHAR(4000)
SET @str=''
SELECT @str=@str+','+CompanyTypeName FROM T_Common_CompanyType
WHERE CompanyType & @ID >0
SET @str=STUFF(@str,1,1,'')
RETURN (@str)
END
GO


DECLARE @MaxType INT
SET @MaxType=(SELECT SUM(CompanyType) FROM T_Common_CompanyType)
SET ROWCOUNT @MaxType

SELECT IDENTITY(int,1,1) as ID INTO #Rows FROM sysobjects


--返回结果
SELECT *,dbo.GetStr(ID) as NAMES FROM #Rows
DROP TABLE #Rows

[cnming]

SET @MaxType=(SELECT SUM(CompanyType) FROM tb)

应该说11楼的做法还是会有一些问题，


例如

Create table tb(CompanyType int,CompanyTypeName nvarchar(6))
Go
Insert into tb
select 1,'船舶代理公司' union all
--select 2,'箱公司' union all
select 4,'货代' 
Go

CREATE FUNCTION GetStr(@ID INT)
RETURNS  NVARCHAR(100)
AS
BEGIN
    DECLARE @str NVARCHAR(100)
    SET @str=''
    SELECT @str=@str+','+CompanyTypeName FROM tb 
    WHERE CompanyType & @ID = CompanyType
    SET @str=STUFF(@str,1,1,'')
    RETURN (@str)
END
GO


DECLARE @MaxType INT
SET @MaxType=(SELECT SUM(CompanyType) FROM tb)

SELECT top(@MaxType) IDENTITY(int,1,1) as ID INTO #Rows FROM sysobjects

--返回结果
SELECT *,dbo.GetStr(ID) as NAMES FROM #Rows

drop function GetStr
drop table tb
drop table #Rows

结果
1 船舶代理公司
2 NULL
3 船舶代理公司
4 货代
5 船舶代理公司,货代


应该是
1 船舶代理公司
4 货代
5 船舶代理公司,货代


还有，当数据是

Insert into tb
--select 1,'船舶代理公司' union all
select 2,'箱公司' union all
select 4,'货代' 

结果

1 NULL
2 箱公司
3 箱公司
4 货代
5 货代
6 箱公司,货代

期望结果是

2 箱公司
4 货代
6 箱公司,货代


麻烦各位再帮我努力一把

[-狙击手-]

/*

Insert into tb
select 1,'船舶代理公司' union all
select 2,'箱公司' union all
select 4,'货代' 


                                           
----------- ------------------------------ 
1           船舶代理公司
2           箱公司
3           船舶代理公司,箱公司
4           货代
5           船舶代理公司,货代
6           箱公司,货代
7           船舶代理公司,箱公司,货代

（所影响的行数为 7 行）



Insert into tb
--select 1,'船舶代理公司' union all
select 2,'箱公司' union all
select 4,'货代' 
----------- ------------------------------ 
2           箱公司
4           货代
6           箱公司,货代

（所影响的行数为 3 行）

Insert into tb
select 1,'船舶代理公司' union all
--select 2,'箱公司' union all
select 4,'货代' 

----------- ------------------------------ 
1           船舶代理公司
4           货代
5           船舶代理公司,货代

（所影响的行数为 3 行）


*/

[-狙击手-]

-- Test Data: tb
If object_id('tb') is not null 
    Drop table tb
Go
Create table tb(CompanyType int,CompanyTypeName nvarchar(6))
Go
Insert into tb
select 1,'船舶代理公司' union all
select 2,'箱公司' union all
select 4,'货代' 
Go
--Start
create function f_count()
returns @t table(id int,col varchar(30))
as
begin
    declare @i int
    select @i= sum(CompanyType) from tb
    declare @s varchar(30)
   
    select @s = isnull(@s+',','')+CompanyTypeName from tb order by CompanyType asc
    insert into @t select @i,@s
    return
end
go

Select case when a.CompanyTypeName = b.CompanyTypeName  then a.CompanyType 
            else a.CompanyType + b.CompanyType end,
       case when a.CompanyTypeName = b.CompanyTypeName then a.CompanyTypeName 
            else
                case when a.CompanyType < b.CompanyType then
                            a.CompanyTypeName+','+b.CompanyTypeName 
                     else b.CompanyTypeName+','+a.CompanyTypeName  
                end
       end
from tb a cross join tb b
where a.CompanyTypeName >= b.CompanyTypeName
union
select * from f_count()
order by 1
drop function f_count

/*

Insert into tb
select 1,'船舶代理公司' union all
select 2,'箱公司' union all
select 4,'货代' 


                                           
----------- ------------------------------ 
1           船舶代理公司
2           箱公司
3           船舶代理公司,箱公司
4           货代
5           船舶代理公司,货代
6           箱公司,货代
7           船舶代理公司,箱公司,货代

（所影响的行数为 7 行）



Insert into tb
--select 1,'船舶代理公司' union all
select 2,'箱公司' union all
select 4,'货代' 
----------- ------------------------------ 
2           箱公司
4           货代
6           箱公司,货代

（所影响的行数为 3 行）

Insert into tb
select 1,'船舶代理公司' union all
--select 2,'箱公司' union all
select 4,'货代' 

----------- ------------------------------ 
1           船舶代理公司
4           货代
5           船舶代理公司,货代

（所影响的行数为 3 行）


*/

[Garnett_KG]

-- 改一下取结果的sql就行了

--返回结果
   SELECT MIN(ID) as ID,NAMES
   FROM 
   (
   	SELECT *,dbo.GetStr(ID) as NAMES FROM #rows
   ) t
   WHERE NAMES IS NOT NULL
   GROUP BY NAMES
   ORDER BY MIN(ID)

[cnming]

[Quote=引用 9 楼 tianhuo_soft 的回复:]
楼主也在为物流公司 做服务

还是在物流公司做IT？
[/Quote]

为物流公司 做服务

[dobear_0922]

Garnett_KG 的函数有问题，应该对照11楼的代码改改：
WHERE CompanyType & @ID >0
-->
WHERE CompanyType & @ID = CompanyType