×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

try this..

with t as(
select 'mross' UID, '2345' Itemid union
select 'mross' UID, '2897' Itemid union
select 'mross' UID, '2678' Itemid union
select 'jford' UID, '3958' Itemid union
select 'jford' UID, '6898' Itemid union
select 'jford' UID, '7582' Itemid union
select 'jford' UID, '2278' Itemid union
select 'jford' UID, '3768' Itemid
),

t2 (Uid, Items) as
(
SELECT UID, STUFF( ( SELECT ','+ Itemid
FROM t a
WHERE b.UID = a.Uid order by itemid asc
FOR XML PATH('')),1 ,1, '') ItemId
FROM t b
GROUP BY Uid
)
select * from t2 inner join t3 on t2.Uid=t3.uid and t2.Items <>t3.items
Report

Replies, comments and Discussions:

  • 工作学习 / 学科技术讨论 / I need compare data in two tables. However, two tables have different structures. Any one has any idea?
    In table one, data are as follows
    UID ItemID
    mross 2345
    mross 2897
    mross 2678
    jford 3958
    jford 6898
    jford 7582
    jford 2278
    jford 3768
    … …

    In table two, data are as follows
    UID Items
    mross 2345, 2678, 2897
    jford 2278, 3958, 6898, 7582
    … …
    • Write a macro
    • Access, sql
    • 写个VB,switch row to column就行了
      • In table two, there is only one spot for all items separated by comma.
        • 你可以把这一cell的内容copy到notepad or word file,replace all commas to spaces, then import to excell again
          • You must be kidding. How can you copy million cells?
            • You didn't say there were million cells. You said only one spot...
              • You never deal with database.
                • To be honest, your question does not seem to asked by a person who is handling database on a daily basis.
    • copy, then paste special, click transpose.
    • Unix way: awk.
    • I need find out missing items in both tables for each user. I think it should have some way to figure out by SQL.
      • You just need to seperate the comma list in table 2 then compare with table 1. In Sql server, you can use CTE and XML to get what you want. Here is an example for your reference.
        • 仰视
        • Since data are put into Test table, why still need CTE?
          • The test table is still comma list data same as your table 2, you either create a temp table to replace comma list with xml format or use CTE instead to convert table 2 to the same structure as table 1, then do except to find the difference.
            • Got it. Thx.
        • Never use SQL's XML type. It's real fantastic.
      • If the structures of the tables are very much alike, I sometimes use UltraEdit to make comparisons.
        • UltraEdit is pretty convenient
    • try this..
      with t as(
      select 'mross' UID, '2345' Itemid union
      select 'mross' UID, '2897' Itemid union
      select 'mross' UID, '2678' Itemid union
      select 'jford' UID, '3958' Itemid union
      select 'jford' UID, '6898' Itemid union
      select 'jford' UID, '7582' Itemid union
      select 'jford' UID, '2278' Itemid union
      select 'jford' UID, '3768' Itemid
      ),

      t2 (Uid, Items) as
      (
      SELECT UID, STUFF( ( SELECT ','+ Itemid
      FROM t a
      WHERE b.UID = a.Uid order by itemid asc
      FOR XML PATH('')),1 ,1, '') ItemId
      FROM t b
      GROUP BY Uid
      )
      select * from t2 inner join t3 on t2.Uid=t3.uid and t2.Items <>t3.items
      • Inner join is for common portion of two set. Obviously, missing items are out of the common portion.
        As a matter fact, outer join is for this kind of task. Since it requires to find out missing items in both sides (Left and right), a full outer join should be used.