with t as( select 'AAA_bb' text from dual) select substr(text, 1, instr(text, '_') - 1), substr(text, instr(text, '_') + 1, length(text) - instr(text, '_')) from t
额..晕,不好意思,表达错误了,应该是: Select distinct A||'_'||B from TableName
with t as( select '11' A, '22' B from dual union all select '11','22' from dual union all select '11','32' from dual ) select distinct A,B from t 难道,你是想这样实在不明白你要干什么?
是这样的,我想把A和B中内容连接起来之后找到不重复的 然后再把A和B分别显示. 我用了Group好像没能成功,所以才用了distinct A||"_"||B 将导出的数据在Excel里分列..类似达到这样的效果..select distinct A,B from t这样不可以的吧? 不好意思,你上面的那串代码我看不懂是啥意思..
比如表的结构: A B abc 123 abc 234 abc 123 得到结果为: A B abc 123 abc 234
select distinct 字段 from 表; 不就可以了,你又联接又分开的,多麻烦
但是distinct是只能作用一个字段的吧? 我想要的效果就是让这个distinct能作用到每一个字段啊.. 可以再多加一个例子: A B abc 123 abc 234 abc 123 bcd 123 得到结果为: A B abc 123 abc 234 bcd 123
distinct就是作用在多个字段上的
--这样 select A,B from tb group by A,B--2. select distinct A,B from tb --在oracle 里面可以 ,mssql则不可以
那奇怪了,我怎么用select distinct A,B from t这样的不可以呢? 我再试一下...
被Lz搞晕了,distinct是作用于所有查询字段的select distinct A,B from TableName 就能达到这种效果
WITH T AS (SELECT 'ABC' A, '123' B FROM DUAL UNION ALL SELECT 'ABC', '234' FROM DUAL UNION ALL SELECT 'ABC', '123' FROM DUAL), C AS (SELECT 'ABC-123' TABLE_NAME FROM DUAL UNION ALL SELECT 'ABC-321' FROM DUAL UNION ALL SELECT 'ABC-123' FROM DUAL UNION ALL SELECT 'ABC-123' FROM DUAL UNION ALL SELECT 'ABC-234' FROM DUAL) SELECT T.A,T.B FROM T WHERE EXISTS (SELECT COUNT(C.TABLE_NAME) FROM C WHERE C.TABLE_NAME = T.A||'-'||T.B HAVING(COUNT(C.TABLE_NAME)=1) GROUP BY C.TABLE_NAME) 看看是否符合你的要求
WITH T AS (SELECT 'ABC' A, '123' B FROM DUAL UNION ALL SELECT 'ABC', '234' FROM DUAL UNION ALL SELECT 'ABC', '123' FROM DUAL), C AS (SELECT 'ABC-123' TABLE_NAME FROM DUAL UNION ALL SELECT 'ABC-321' FROM DUAL UNION ALL SELECT 'ABC-123' FROM DUAL UNION ALL SELECT 'ABC-123' FROM DUAL UNION ALL SELECT 'ABC-234' FROM DUAL) SELECT T.A,T.B FROM T WHERE EXISTS (SELECT 1 FROM C WHERE C.TABLE_NAME = T.A||'-'||T.B HAVING(COUNT(C.TABLE_NAME)=1) GROUP BY C.TABLE_NAME) 看这个,为什么不能编辑自己发的帖?
with t as(
select 'AAA_bb' text from dual)
select substr(text, 1, instr(text, '_') - 1),
substr(text, instr(text, '_') + 1, length(text) - instr(text, '_'))
from t
Select distinct A||'_'||B from TableName
with t as(
select '11' A, '22' B from dual
union all select '11','22' from dual
union all select '11','32' from dual
)
select distinct A,B from t
难道,你是想这样实在不明白你要干什么?
然后再把A和B分别显示.
我用了Group好像没能成功,所以才用了distinct A||"_"||B
将导出的数据在Excel里分列..类似达到这样的效果..select distinct A,B from t这样不可以的吧?
不好意思,你上面的那串代码我看不懂是啥意思..
A B
abc 123
abc 234
abc 123
得到结果为:
A B
abc 123
abc 234
我想要的效果就是让这个distinct能作用到每一个字段啊..
可以再多加一个例子:
A B
abc 123
abc 234
abc 123
bcd 123
得到结果为:
A B
abc 123
abc 234
bcd 123
select A,B from tb group by A,B--2.
select distinct A,B from tb
--在oracle 里面可以 ,mssql则不可以
我再试一下...
被Lz搞晕了,distinct是作用于所有查询字段的select distinct A,B from TableName 就能达到这种效果
(SELECT 'ABC' A, '123' B
FROM DUAL
UNION ALL
SELECT 'ABC', '234'
FROM DUAL
UNION ALL
SELECT 'ABC', '123' FROM DUAL),
C AS
(SELECT 'ABC-123' TABLE_NAME
FROM DUAL
UNION ALL
SELECT 'ABC-321'
FROM DUAL
UNION ALL
SELECT 'ABC-123'
FROM DUAL
UNION ALL
SELECT 'ABC-123'
FROM DUAL
UNION ALL
SELECT 'ABC-234' FROM DUAL)
SELECT T.A,T.B
FROM T
WHERE EXISTS (SELECT COUNT(C.TABLE_NAME)
FROM C
WHERE C.TABLE_NAME = T.A||'-'||T.B HAVING(COUNT(C.TABLE_NAME)=1)
GROUP BY C.TABLE_NAME)
看看是否符合你的要求
(SELECT 'ABC' A, '123' B
FROM DUAL
UNION ALL
SELECT 'ABC', '234'
FROM DUAL
UNION ALL
SELECT 'ABC', '123' FROM DUAL),
C AS
(SELECT 'ABC-123' TABLE_NAME
FROM DUAL
UNION ALL
SELECT 'ABC-321'
FROM DUAL
UNION ALL
SELECT 'ABC-123'
FROM DUAL
UNION ALL
SELECT 'ABC-123'
FROM DUAL
UNION ALL
SELECT 'ABC-234' FROM DUAL)
SELECT T.A,T.B
FROM T
WHERE EXISTS (SELECT 1
FROM C
WHERE C.TABLE_NAME = T.A||'-'||T.B HAVING(COUNT(C.TABLE_NAME)=1)
GROUP BY C.TABLE_NAME)
看这个,为什么不能编辑自己发的帖?