第一个问题你尝试下用下面这个SQL语句看看能不能解决: SELECT 表1.名称&表2.名称 AS 结果 FROM 表1,表2 where 表1.名称>表2.名称
第一个问题: select a.名称+b.名称 from 表1 a,表2 b where a.名称<>b.名称 and a.名称<b.名称 order by a.名称,b.名称
问题2 create table tb1 (字段1 varchar(10),字段2 varchar(10)) create table tb2 (id int,名称 varchar(10)) insert into tb1 ....... insert into tb2 ....... create FUNCTION dbo.f_str(@id int) RETURNS varchar(8000) AS BEGIN DECLARE @r varchar(8000) SET @r = '' SELECT @r = isnull(@r,'') + 名称 FROM tb2 WHERE id=@id --order by 名称 RETURN @r END GOSELECT a.字段1,a.字段2,COUNT(b.名称) 次数 FROM tb1 a left join (select ID,dbo.f_str(id) 名称 from tb2 group by id) b on CHARINDEX(a.字段1,b.名称)>0 and CHARINDEX(a.字段2,b.名称)>0 group by a.字段1,a.字段2
create table #temp1([name] varchar(10)) insert #temp1 select 'A' union all select 'B' union all select 'C' union all select 'D' create table #temp2([name] varchar(10)) insert #temp2 select 'A' union all select 'B' union all select 'C' union all select 'D' go --SQL: --#1. ;with cte_1 as ( select rowno = row_number() over (order by a.name+b.name), result = a.name+b.name from #temp1 a, #temp2 b where a.name <> b.name ) select * from cte_1 a where not exists(select 1 from cte_1 where REVERSE(result) = a.result and rowno < a.rowno) /* rowno result 1 AB 2 AC 3 AD 5 BC 6 BD 9 CD */
create table #temp1([F1] varchar(10), [F2] varchar(10)) insert #temp1 select 'A', 'B' union all select 'A', 'C' union all select 'A', 'D' create table #temp2(id int, [name] varchar(10)) insert #temp2 select 1, 'A' union all select 2,'A' union all select 2, 'B' union all select 3,'A' union all select 3, 'B' union all select 3,'C' GO --SQL: select * from #temp1 a outer apply (select cnt =count(*) from #temp2 b, #temp2 c where b.[name] = a.F1 and c.name= a.F2 and b.id = c.id) b /* F1 F2 cnt A B 2 A C 1 A D 0 */
a.名称<>b.名称 and a.名称<b.名称 这两个意思应该是一样的吧?如果a.名称<b.名称存在,肯定就会:a.名称<>b.名称,为什么还要单独判断呢?
SELECT 表1.名称&表2.名称 AS 结果 FROM 表1,表2 where 表1.名称>表2.名称
select a.名称+b.名称 from 表1 a,表2 b
where a.名称<>b.名称
and a.名称<b.名称
order by a.名称,b.名称
create table tb1 (字段1 varchar(10),字段2 varchar(10))
create table tb2 (id int,名称 varchar(10))
insert into tb1 .......
insert into tb2 .......
create FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @r varchar(8000)
SET @r = ''
SELECT @r = isnull(@r,'') + 名称 FROM tb2 WHERE id=@id --order by 名称
RETURN @r
END
GOSELECT a.字段1,a.字段2,COUNT(b.名称) 次数
FROM tb1 a left join (select ID,dbo.f_str(id) 名称 from tb2 group by id) b
on CHARINDEX(a.字段1,b.名称)>0 and CHARINDEX(a.字段2,b.名称)>0
group by a.字段1,a.字段2
insert #temp1
select 'A' union all select 'B' union all select 'C' union all select 'D'
create table #temp2([name] varchar(10))
insert #temp2
select 'A' union all select 'B' union all select 'C' union all select 'D'
go
--SQL:
--#1.
;with cte_1 as
(
select
rowno = row_number() over (order by a.name+b.name),
result = a.name+b.name from #temp1 a, #temp2 b
where a.name <> b.name
)
select * from cte_1 a
where not exists(select 1 from cte_1 where REVERSE(result) = a.result and rowno < a.rowno)
/*
rowno result
1 AB
2 AC
3 AD
5 BC
6 BD
9 CD
*/
insert #temp1
select 'A', 'B' union all select 'A', 'C' union all select 'A', 'D'
create table #temp2(id int, [name] varchar(10))
insert #temp2
select 1, 'A' union all select 2,'A' union all
select 2, 'B' union all select 3,'A' union all
select 3, 'B' union all select 3,'C'
GO
--SQL:
select * from #temp1 a
outer apply
(select cnt =count(*) from #temp2 b, #temp2 c where b.[name] = a.F1 and c.name= a.F2 and b.id = c.id) b
/*
F1 F2 cnt
A B 2
A C 1
A D 0
*/
and a.名称<b.名称
这两个意思应该是一样的吧?如果a.名称<b.名称存在,肯定就会:a.名称<>b.名称,为什么还要单独判断呢?