条件也没说清楚,是这样吗?create table tb(AID int,ANAME varchar(10),ASN char(1))insert tb values(1,'aa','1')
insert tb values(2,'aa','3')
insert tb values(3,'aa','7')
insert tb values(4,'bb','A')
insert tb values(5,'bb','B')select distinct a.ASN+b.ASN CSN from tb a cross join tb b where a.aname<>b.aname and a.asn like '[0-9]'
/*
CSN
----
1A
1B
3A
3B
7A
7B
--*/
insert tb values(2,'aa','3')
insert tb values(3,'aa','7')
insert tb values(4,'bb','A')
insert tb values(5,'bb','B')select distinct a.ASN+b.ASN CSN from tb a cross join tb b where a.aname<>b.aname and a.asn like '[0-9]'
/*
CSN
----
1A
1B
3A
3B
7A
7B
--*/
insert into #tb
select '1','aa','1'
union all select '2','aa','3'
union all select '3','aa','7'
union all select '4','bb','A'
union all select '5','bb','B'select * from #tbselect aid=identity(int,1,1),asn
into #t
from
(
select a.asn+b.asn as asn
from
(select asn from #tb where isnumeric(asn)=1)a,
(select asn from #tb where isnumeric(asn)=0)b
)t
order by 1select * from #t
aid asn
-----------------------
1 1A
2 3A
3 7A
4 1B
5 3B
6 7Bdrop table #tb,#t
INSERT @TB
SELECT 1, 'aa', '1' UNION ALL
SELECT 2, 'aa', '3' UNION ALL
SELECT 3, 'aa', '7' UNION ALL
SELECT 4, 'bb', 'A' UNION ALL
SELECT 5, 'bb', 'B'SELECT CID=ROW_NUMBER() OVER (ORDER BY A.ASN,B.ASN),A.ASN+B.ASN AS CSN
FROM (
SELECT * FROM @TB
WHERE PATINDEX('%[A-Z]%',ASN)=0) A,
(SELECT * FROM @TB
WHERE PATINDEX('%[A-Z]%',ASN)>0) B
ORDER BY A.ASN,B.ASN
/*
CID CSN
-------------------- ----
1 1A
2 1B
3 3A
4 3B
5 7A
6 7B
*/
1 aa 1
2 aa 3
3 aa 7
4 bb A
5 bb B
…… 表C
CID CSN
1 1A
2 1B
3 3A
4 3B
5 7A
6 7B
……其实就是根据表A字段ANAME的不同,对字段ASN进行组合,获得 CSN 1A,1B,3A,3B,7A,7B
1 aa 1
2 aa 3
3 aa 7
4 bb A
5 bb B
6 cc k
…… 表C
CID CSN
1 1A
2 1B
3 3A
4 3B
5 7A
6 7B
……
表A字段ANAME 中还有 cc,我只要取出 aa与bb对应的ASN进行组合,得到CSN 1A,1B,3A,3B,7A,7B请帮我再做下修改 谢谢。
where ANAME<>'cc'
go
create table [A]([AID] int,[ANAME] varchar(2),[ASN] varchar(1))
insert [A]
select 1,'aa','1' union all
select 2,'aa','3' union all
select 3,'aa','7' union all
select 4,'bb','A' union all
select 5,'bb','B' union all
select 6,'cc','k'
go
--select * from [A]select CID=row_number() over(order by a.ASN,b.ASN),CSN=a.ASN+b.ASN
from(select ASN from A where ANAME='aa') a
,(select ASN from A where ANAME='bb') b
/*
CID CSN
-------------------- ----
1 1A
2 1B
3 3A
4 3B
5 7A
6 7B(6 行受影响)
*/
INSERT @TB
SELECT 1, 'aa', '1' UNION ALL
SELECT 2, 'aa', '3' UNION ALL
SELECT 3, 'aa', '7' UNION ALL
SELECT 4, 'bb', 'A' UNION ALL
SELECT 5, 'bb', 'B' UNION ALL
SELECT 6, 'cc', 'k'
SELECT A.ASN+B.ASN AS CSN
FROM (SELECT * FROM @TB WHERE ISNUMERIC([ASN])=1) A
,(SELECT * FROM @TB WHERE ISNUMERIC([ASN])=0 AND [ANAME]!='CC')B
ORDER BY A.[ASN]
/*CSN
----
1A
1B
3B
3A
7A
7B*/
DECLARE @TB TABLE([AID] INT, [ANAME] VARCHAR(2), [ASN] VARCHAR(1))
INSERT @TB
SELECT 1, 'aa', '1' UNION ALL
SELECT 2, 'aa', '3' UNION ALL
SELECT 3, 'aa', '7' UNION ALL
SELECT 4, 'bb', 'A' UNION ALL
SELECT 5, 'bb', 'B'SELECT CID=IDENTITY(int,1,1),A.ASN+B.ASN AS CSN
INTO #
FROM (
SELECT * FROM @TB
WHERE PATINDEX('%[A-Z]%',ASN)=0 AND ANAME<>'cc') A,
(SELECT * FROM @TB
WHERE PATINDEX('%[A-Z]%',ASN)>0 AND ANAME<>'cc') B
ORDER BY A.ASN,B.ASNSELECT * FROM #
DROP TABLE #
/*
CID CSN
----------- ----
1 1A
2 1B
3 3A
4 3B
5 7A
6 7B
*/