是否可以输出sql查询？

Question

我认为这是不可能的，但我需要这样的结果：

有两张桌子：

值为work_item(work_item_id, name)的([529,DD],[539,ER])

值为work_item_party(work_item_id,party_type,party_name,country)的([1],[2],[3],[4],[5],[6],[7])

哪里

[1] = (529,applicant,mac,us)
[2] = (529,consignee,l-communication,a)
[3] = (529,end-user,u-a,a)
[4] = (539,applicant,dell,us)
[5] = (539,consignee,l-touch,b)
[6] = (539,end-user,u-b,b)
[7] = (539,end-user,c-b,b)

这是我需要的输出：

"work_item_id","name","applicant","consignee",end-user,"country"
(
[529,DD,mac,l-communication,u-a,a],
[539,ER,dell,l-touch,u-b,b],
[539,ER,dell,l-touch,c-b,b]
)

我可以为party_type是申请人、收货人或最终用户的每一种情况提取数据，但是很难将它们结合起来作为上面的输出。你觉得这有可能。如果是，你能给我看看你的疑问吗？

非常感谢

圣诞快乐！

Answer 1

SQL Fiddle

Oracle 11g R2架构设置

CREATE TABLE work_item(work_item_id, name) AS
          SELECT 529,'DD' FROM DUAL
UNION ALL SELECT 539,'ER' FROM DUAL;

CREATE TABLE work_item_party(work_item_id,party_type,party_name,country) AS
          SELECT 529,'applicant','mac','us' FROM DUAL
UNION ALL SELECT 529,'consignee','l-communication','a' FROM DUAL
UNION ALL SELECT 529,'end-user','u-a','a' FROM DUAL
UNION ALL SELECT 539,'applicant','dell','us' FROM DUAL
UNION ALL SELECT 539,'consignee','l-touch','b' FROM DUAL
UNION ALL SELECT 539,'end-user','u-b','b' FROM DUAL
UNION ALL SELECT 539,'end-user','c-b','b' FROM DUAL;

查询1

WITH applicant_consignees AS (
  SELECT p.work_item_id,
         i.name,
         MAX( CASE party_type WHEN 'applicant' THEN party_name END ) AS applicant,
         MAX( CASE party_type WHEN 'consignee' THEN party_name END ) AS consignee
  FROM   work_item i
         INNER JOIN
         work_item_party p
         ON ( i.work_item_id = p.work_item_id )
  WHERE  party_type IN ('applicant', 'consignee')
  GROUP BY p.work_item_id, i.name
),
end_users AS (
  SELECT work_item_id,
         party_name AS "end-user",
         country
  FROM   work_item_party
  WHERE  party_type = 'end-user'
)
SELECT a.*,
       e."end-user",
       e.country
FROM   applicant_consignees a
       INNER JOIN
       end_users e
       ON ( a.work_item_id = e.work_item_id )
ORDER BY a.work_item_id, name, country

结果

| WORK_ITEM_ID | NAME | APPLICANT |       CONSIGNEE | END-USER | COUNTRY |
|--------------|------|-----------|-----------------|----------|---------|
|          529 |   DD |       mac | l-communication |      u-a |       a |
|          539 |   ER |      dell |         l-touch |      u-b |       b |
|          539 |   ER |      dell |         l-touch |      c-b |       b |

Answer 2

这个怎么样。

Select A.work_item_id, A.name, B.party_name, C.party_name, D.party_name, D.Country
 From work_item as A
   left outer join work_item_party as B on A.work_item_id = B.work_item_id and B.party_type = 'applicant'
   left outer join work_item_party as C on A.work_item_id = C.work_item_id and C.party_type = 'consignee'
   left outer join work_item_party as D on A.work_item_id = D.work_item_id and D.party_type = 'end-user'

我希望你在寻找最终用户的国家.您也可以使用内部连接，如果您确信每个work_item_id在Work_item_party表中的每个Work_item_party至少有一个记录