让人头痛的问题 帮帮忙 很急！

KennethTC

Table 1
ID  |  Country
---------------
1   |   French， China，Japan
2   |  Malaysia, Singapore

Table 2
ID  |  Country1
---------------
1   |  Japan， China


Result i want :
ID  |  Country
---------------
1   |   French, Malaysia, Singapore

请问要如何 compare table1 和 table 2 的 attributes 里的record ， 然后 从中 拿出 不match 的？
请帮帮忙

谷人

如果你是用PL/SQL 或 將它搬至external language ( eg dataset, recordset ) 去處理﹐ 會很簡單。

要用純SQL的方法﹐ 就蠻瑣的。。但還是有辦法的﹐ 方法還不止一種。基本上, 只要data是在Relational DB 裡頭﹐ 沒有多少東西SQL是辦不到的。。。

谷人用的是Oracle的方式。regexp_replace() 函數要10g 以上才有。

主要是3個步驟﹕
1) 將 data 從delimited string轉換成 column rows
2) 用 set operator 比較 兩個 table
3) 將(2) 的 result 轉換回delimited string

步驟(1) 的方法﹐ 谷人是參考 http://www.sqlsnippets.com/en/topic-11983.html 的招術 ( 感謝這些神 )

-- assumptions: 1) delimiter must be ', '
--                     2) the final result is only one single row.
-- emulate the table data
create table COUNTRY_LIST
( ID number(2) not null,
  LIST varchar2(30) not null,
  constraint COUNTRY_LIST_PK
   primary key (ID)
);

create table OTHER_COUNTRY_LIST
( ID number(2) not null,
  LIST varchar2(30) not null,
  constraint OTHER_COUNTRY_LIST_PK
   primary key (ID)
);

insert into COUNTRY_LIST values (1, 'French, China, Japan');
insert into COUNTRY_LIST values (2, 'Malaysia, Singapore');

insert into OTHER_COUNTRY_LIST values (1, 'Japan, China');

-- for the use of joining
create table INTEGERS
( INTEGER_VALUE integer primary key
)
organization index;

-- pl/sql to populate INTEGERS table
begin
  for i in -100 .. 100 loop
    insert into integers values ( i );
  end loop;
  commit;
end;
/

-- the single-statement solution to your problem
with ranked_list
as
( select list_value,
   row_number() over (order by list_value) rn,
   count(*) over () cnt
   from (
select a.country as list_value
from
(select
substr(', ' || c1.LIST || ', ', instr(', ' || c1.LIST || ', ', ',', 1, i.integer_value) + 2, instr(', ' || c1.LIST || ', ', ',', 1, i.integer_value + 1) - (instr(', ' || c1.LIST || ', ', ',', 1, i.integer_value) + 2)) as country
from
(select c.ID, c.LIST,
length(regexp_replace( c.LIST || ',', '[^' || ',' || ']', NULL)) as element_count
from COUNTRY_LIST c
) c1,
(select INTEGER_VALUE from INTEGERS where integer_value > 0
and integer_value <=
(select sum(length(regexp_replace( LIST || ',', '[^' || ',' || ']', NULL))) from COUNTRY_LIST)
) i
where i.integer_value <= c1.element_count) a
minus
select b.country
from
(select
substr(', ' || c1.LIST || ', ', instr(', ' || c1.LIST || ', ', ',', 1, i.integer_value) + 2, instr(', ' || c1.LIST || ', ', ',', 1, i.integer_value + 1) - (instr(', ' || c1.LIST || ', ', ',', 1, i.integer_value) + 2)) as country
from
(select c.ID, c.LIST,
length(regexp_replace( c.LIST || ',', '[^' || ',' || ']', NULL)) as element_count
from OTHER_COUNTRY_LIST c
) c1,
(select INTEGER_VALUE from INTEGERS where integer_value > 0
and integer_value <=
(select sum(length(regexp_replace( LIST || ',', '[^' || ',' || ']', NULL))) from OTHER_COUNTRY_LIST)
) i
where i.integer_value <= c1.element_count) b
   )
)
select 1 "ID", ltrim(sys_connect_by_path(list_value, ', '),', ') "Country"
   from ranked_list
   where rn = cnt
   start with rn = 1
   connect by prior rn = rn - 1;
--------------------------------------------------------------------

soonyu

SQL can do ? Yes, SQL 2005 can do..
I guess got more simple way


create table test1
(id int,
country varchar(50))
create table test2
(id int,
country varchar(50))
insert into test1 values (1,'French, China,Japan')
insert into test1 values (2,'Malaysia, Singapore')
insert into test2 values (1,'Japan, China')


WITH extract1 (country_Attributes) AS
(
SELECT
      CONVERT(XML,'<country><Attribute>'
        + REPLACE(country,',', '</Attribute><Attribute>')
        + '</Attribute></country>') AS country_Attributes
FROM test1
union all
SELECT
      CONVERT(XML,'<country><Attribute>'
        + REPLACE(country,',', '</Attribute><Attribute>')
        + '</Attribute></country>') AS country_Attributes
FROM test2
)
SELECT
    country_Attributes.value('/country[1]/Attribute[1]','varchar(25)') AS a,
    country_Attributes.value('/country[1]/Attribute[2]','varchar(25)') AS b,
    country_Attributes.value('/country[1]/Attribute[3]','varchar(25)') AS c,
    country_Attributes.value('/country[1]/Attribute[4]','varchar(25)') AS d
into #temp
FROM extract1;

select country into #temp2 from
(SELECT rtrim(ltrim(colval)) as country
FROM
(SELECT a, b,c,d
FROM #temp) p
UNPIVOT
(ColVal FOR Col IN
(a, b, c,d)
)AS unpvt) tmp
group by country
having count(1)<2

declare @retstr varchar(8000)  
select @retstr =  COALESCE(@retstr + ',','') + country
from #temp2
print @retstr

drop table #temp
drop table #temp2

[ 本帖最后由 soonyu 于 11-9-2009 06:04 PM 编辑 ]

TSHsoft

用逗号分开国家后再进行分析只会让系统变慢而已。

其实应该要这样insert record才对：
Table 1
ID  |  Country
---------------
1   |  French
2   |  China
3   |  Japan
4   |  Malaysia
5   |  Singapore

Table 2
ID  |  Country
---------------
1   |  Japan
2   |  China
3   |  India

方法：
SELECT Table1.ID,Table1.Country FROM Table1,Table2 WHERE Table1.Country <> Table2.Country
UNION
SELECT Table2.ID,Table2.Country FROM Table1,Table2 WHERE Table1.Country <> Table2.Country

Result:
ID  |  Country
---------------
1   |  French
4   |  Malaysia
5   |  Singapore
3   |  India

KennethTC

原帖由 TSHsoft 于 13/9/2009 02:15 PM 发表
用逗号分开国家后再进行分析只会让系统变慢而已。

其实应该要这样insert record才对：
Table 1
ID  |  Country
---------------
1   |  French
2   |  China
3   |  Japan
4   |  Malaysia
5   |  Singap ...

这个方法是容易， 可是在我的database 里， 那个table 的 record是支持很多个 country 的 所以没用。

KennethTC

原帖由 soonyu 于 11/9/2009 05:58 PM 发表
SQL can do ? Yes, SQL 2005 can do..
I guess got more simple way


create table test1
(id int,
country varchar(50))
create table test2
(id int,
country varchar(50))
insert into test1 values ( ...

好复杂。。。要点时间消化　我在ｐｒｅｓｅｎｔａｔｉｏｎ时有问过老师　这个问题，　他说你用　ＬＩＫＥ　就可以做到，　可是就是想不到

[ 本帖最后由 KennethTC 于 17-9-2009 12:45 AM 编辑 ]

KennethTC

原帖由 谷人 于 13/8/2009 04:42 PM 发表
如果你是用PL/SQL 或 將它搬至external language ( eg dataset, recordset ) 去處理﹐ 會很簡單。

要用純SQL的方法﹐ 就蠻瑣的。。但還是有辦法的﹐ 方法還不止一種。基本上, 只要data是在Relational DB 裡頭﹐ 沒 ...

ＰＬ　ＳＱＬ　的方法我也找到一些　可是那些方法就是不能用在ｍｓｓｑｌ　

ｏｒａｃｌｅ　我没学过　不太清楚

TSHsoft

你用什么语言配合mysql？
php、C++、C#、VB、java、还是其他的？

一个query就想把结果叫出来的话，应该是不可能的了。如谷人所说的，用外部语言处理了再暂存去table，然后就可以很容易得到结果了。

KennethTC

我是用 mssql
没办法 学校的功课需求 一定要用sql query 去得到result 不然也不头痛

TSHsoft

原帖由 KennethTC 于 19-9-2009 11:33 PM 发表
我是用 mssql
没办法 学校的功课需求 一定要用sql query 去得到result 不然也不头痛

可以用procedure吗？

vampcheah

原帖由 KennethTC 于 17-9-2009 12:42 AM 发表


这个方法是容易， 可是在我的database 里， 那个table 的 record是支持很多个 country 的 所以没用。

但是你这样的分布已经不 normalize 了吧。
database design 就是要先把所有 table 做好 normalization 才进行处理吧。

cristiano~7

原帖由 KennethTC 于 19-9-2009 11:33 PM 发表
我是用 mssql
没办法 学校的功课需求 一定要用sql query 去得到result 不然也不头痛

取巧的话一个sql query就可以完成了

以下是半完成品（只是从一个字串分成几个record），可以参考看看

select countryname
from
(
select substr(a.country, 1, locate(', ',a.country,1) - 1) countryname, @rownum := 0
from countryA a
union all
select trim(replace(substr(a.country, b.rownum, locate(', ',a.country,b.rownum)), ',', '')) countryname, b.rownum
from countryA as a, (select @rownum := @rownum + 1 as rownum from information_schema.columns limit 255) as b
where length(a.country) >= b.rownum
and locate(',',a.country,b.rownum) = b.rownum
)
as countrylist

[ 本帖最后由 cristiano~7 于 30-9-2009 12:18 AM 编辑 ]

rman

這個資料庫本身設計就有問題
違背了 RDBMS 之父 codd 原則第一條:
All information in the database is to be represented in one and only one way, namely by values in column positions within rows of tables
所以要用純 SQL 來查詢實在難

kennynjc

可以用number table 来解决

骇客小子

原帖由 rman 于 3-10-2009 07:50 PM 发表
這個資料庫本身設計就有問題
違背了 RDBMS 之父 codd 原則第一條:
All information in the database is to be represented in one and only one way, namely by values in column positions within rows of tables ...

我极度同意楼主在浪费电脑资源， 做无聊的split string 还然后compare 的事情

兔仙人

你 可以 做 一个 FUNCTIOn 然后 RETURN 回 DATA 就 搞掂了 。。

p.s : 刚刚跟一个 SQL高手 学得

stephanie83

用这个吧
select * from table1 where id not in
(select id from table2)

ReyesChuah

會用這種方式 收資料真的有點... query time 都用了多少時間了
回 17# 你這樣是拿不到他要的，感覺只有store proc 可以做到而已。

tandan999

先分出来，然后在写个SQL语句