mysql - 如何挑选字段中的特定条目以创建新的输出列 (SQL)

Question

不知道你能不能帮我解决这个问题，这是我的第一篇文章，所以请放轻松 :-)

我想调整此查询以分隔可能出现在“服务代码”字段中的某些值，并在输出时将它们放入单独的列中。

我调整了一个报告模板，使我可以轻松地输出数据。

使用的代码遵循 _IHPNR001A 的格式，最后两个数字区分预期的列。所以

• 1-15 将是访问类型，
• 16-69 将是 Services 1 和 Services 2 如果有第二次出现，
• 70-99 将是 Rec Program。

我提取的所有其他数据字段(即 DOB)也需要仍然存在，但我想根据“ID”字段压缩数据。

其内容如下……

SELECT b.id as 'ID', bd.service_code as 'Service Code', bs.description as 'Service Desc', b.demographic_no as 'Demo No', b.demographic_name as 'Demo Name', b.provider_no as 'Provider No', b.dob as 'DOB', demo.sex as 'Gender', b.appointment_no as 'Appt No',   bd.dx as 'diag. Code', b.billing_date as 'Billing Date'

FROM billing_on_cheader1 b, billing_on_item bd, billingservice bs, demographic demo

WHERE b.provider_no = {provider_no} and b.id = bd.ch1_id 
   and b.status <> 'D' 
   and b.billing_date >='{billing_date_start}' and b.billing_date     <='{billing_date_end}' 
   and bs.service_code = bd.service_code
   and b.demographic_no = demo.demographic_no;

    </query>
  <param id="provider_no" type="list" description="Provider Number">
  <param-query>select provider_no, CONCAT(last_name, ', ', first_name, '     (', provider_no, ')') from provider order by last_name;</param-query>
   </param>
   <param id="billing_date_start" type="date" description="Billing date start">
   </param>
   <param id="billing_date_end" type="date" description="Billing date end">
   </param>
    </report>

目前的结果是:

我的预期结果是:

我尝试应用建议:

<report title="IHP STATS COLLECTION v13" description="IHP Stats Test -(Version 13)" active="1">

SELECT
b.demographic_no as 'Demo No', b.demographic_name as 'Demo Name', b.dob as 'DOB', demo.sex as 'Gender', b.provider_no as 'Provider No', bd.dx as 'diag. Code', b.billing_date as 'Billing Date',
bdtv.ch1_id as 'ID', bdtv.service_code as 'Type of Visit',
bds1.service_code as 'Services 1',
bds2.service_code as 'Services 2',
bdrp.service_code as 'Rec Program'
FROM billing_on_item bdtv, billing_on_cheader1 b, billing_on_item bd, billingservice bs, demographic demo
LEFT JOIN billing_on_item bds1 
 ON bds1.ch1_id = bdtv.ch1_id 
 AND SUBSTRING(bds1.service_code, -3, 2) BETWEEN 16 AND 69
LEFT JOIN billing_on_item bds2 
 ON bds2.ch1_id = bdtv.ch1_id 
 AND SUBSTRING(bds2.service_code, -3, 2) BETWEEN 16 AND 69
 AND bds2.service_code <> bds1.service_code 
LEFT JOIN billing_on_item bdrp 
 ON bdrp.ch1_id = bdtv.ch1_id AND SUBSTRING(bdrp.service_code, -3, 2) BETWEEN 70 AND 99
WHERE SUBSTRING(bdtv.service_code, -3, 2) BETWEEN 0 AND 15
AND b.provider_no = {provider_no} and b.id = bd.ch1_id 
   and b.status <> 'D' 
   and b.billing_date >='{billing_date_start}' and b.billing_date <='{billing_date_end}' 
   and bs.service_code = bd.service_code
and b.demographic_no = demo.demographic_no;
GROUP BY bdtv.ch1_id;

</query>
  <param id="provider_no" type="list" description="Provider Number">
  <param-query> select provider_no, CONCAT(last_name, ', ', first_name, ' (', provider_no, ')') from provider order by last_name;</param-query>
   </param>
   <param id="billing_date_start" type="date" description="Billing date start">
   </param>
   <param id="billing_date_end" type="date" description="Billing date end">
   </param>
</report>

Answer 1

基本上你想转置表格 - 例如根据某些行标准将行变成列。

您可以通过自己多次加入 billing_on_item 表来实现。类似的东西

SELECT 
bdtv.ch1_id as 'ID', bdtv.service_code as 'Type of Visit',
bds1.service_code as 'Services 1',
bds2.service_code as 'Services 2',
bdrp.service_code as 'Rec Program'
FROM billing_on_item bdtv
LEFT JOIN billing_on_item bds1 
     ON bds1.ch1_id = bdtv.ch1_id 
     AND SUBSTRING(bds1.service_code, -3, 2) BETWEEN 16 AND 69
LEFT JOIN billing_on_item bds2 
     ON bds2.ch1_id = bdtv.ch1_id 
     AND SUBSTRING(bds2.service_code, -3, 2) BETWEEN 16 AND 69
     AND bds2.service_code <> bds1.service_code 
LEFT JOIN billing_on_item bdrp 
     ON bdrp.ch1_id = bdtv.ch1_id AND SUBSTRING(bdrp.service_code, -3, 2) BETWEEN 70 AND 99
WHERE SUBSTRING(bdtv.service_code, -3, 2) BETWEEN 0 AND 15
GROUP BY bdtv.ch1_id

如此处所示:SQLFiddle

更新了涉及所有表格的答案:

SELECT b.demographic_name as 'Demo Name', b.dob as 'DOB', 
demo.sex as 'Gender', 
b.provider_no as 'Provider No', bdtv.dx as 'diag. Code', 
b.billing_date as 'Billing Date',
bdtv.ch1_id as 'ID', bdtv.service_code as 'Type of Visit',
bds1.service_code as 'Services 1',
bds2.service_code as 'Services 2',
bdrp.service_code as 'Rec Program'
FROM billing_on_cheader1 b
JOIN demographic demo ON b.demographic_no = demo.demographic_no
JOIN billing_on_item bdtv ON b.id = bdtv.ch1_id
LEFT JOIN billing_on_item bds1 
 ON bds1.ch1_id = bdtv.ch1_id 
 AND SUBSTRING(bds1.service_code, -3, 2) BETWEEN 16 AND 69
LEFT JOIN billing_on_item bds2 
 ON bds2.ch1_id = bdtv.ch1_id 
 AND SUBSTRING(bds2.service_code, -3, 2) BETWEEN 16 AND 69
 AND bds2.service_code <> bds1.service_code 
LEFT JOIN billing_on_item bdrp 
 ON bdrp.ch1_id = bdtv.ch1_id AND SUBSTRING(bdrp.service_code, -3, 2) BETWEEN 70 AND 99
WHERE SUBSTRING(bdtv.service_code, -3, 2) BETWEEN 0 AND 15
AND b.provider_no = 54 AND b.status <> 'D'
GROUP BY bdtv.ch1_id;

SQLFiddle