douzhi1813
2018-05-31 15:39 阅读 68
已采纳

连接查询以将多行中的数据转换为单行

Selecting data from multiple rows into a single row

Quote_ws table

quote_ws_id     quote_id    gross       tax
101                 79      98.25       13.0
102                 79      91.25       12.5    
103                 79      94.25       11.0
104                 79      92.25       11.5
105                 79      96.25       12.0


Section_table

section_id     quote_id    lsb(quote_ws_id)   non_lsb(quote_ws_id)
1               79          101                     null
2               79          102                     103
3               79          104                     105

I am trying to create a MYSQL query that will return gross and tax values from table "Quote_ws" based on lsb and non lsb values in 'Section' table.The results would be:

section_id     quote_id    gross                tax
1               79          98.25               13.0


2               79          91.25 + 94.25       12.5+11.0
                        (lsb 102,non-lsb 103)   (lsb 102,non-lsb 103)

3               79          92.25 + 96.25       11.5+12.0       
                        (lsb 104,non-lsb 105)   (lsb 104,non-lsb 105)

I am new to SQL. How can I do that? Please help me ??

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    dsfs504545 dsfs504545 2018-05-31 16:02
    SELECT
    s.section_id,
    s.quote_id,
    (IFNULL(q_lbs.gross, 0)+IFNULL(q_non_lbs.gross, 0)) as gross,
    (IFNULL(q_lbs.tax, 0)+IFNULL(q_non_lbs.tax, 0)) as tax,
    FROM
    Section_table s
    
    LEFT JOIN Quote_ws q_lbs
    ON q.quote_id = q.quote_id
    
    LEFT JOIN Quote_ws q_non_lbs
    ON q.quote_id = q.quote_id;
    
    点赞 评论 复制链接分享

相关推荐