如何在SQL的同一行中处理具有不同值的列? [英] How to go about a column with different values in a same row in sql?

问题描述

我的桌子具有以下结构

empno    empname    loan ref   amount
1        abc        123        100
1        abc        456        200.

即员工可以利用两笔贷款(例如汽车和摩托车).

i.e an employee can avail two loans(say car and scooter).

预期输出:

empno   empname      loan ref1   amt    loanref2    amt
1       abc           120        100    456         200 

避免重复的empno重复.如何在sql中进行操作??

to avoid duplicate empno repeating. How to go about it in sql???

推荐答案

关于表设计的先前注释–实际上，表中存在冗余；您可以将empname存储在另一个表中，您可以将其与此处的表联接以避免这种情况；每个冗余都是潜在的矛盾. 但是，如果我们针对查询和最小化必要的联接进行了优化的表设计，则可以将其从其他位置填充到批处理作业中，然后该设计将是适当的.

Concerning the previous comments on table design - there is, in fact, a redundancy in the table; you could store the empname in another table, which you would join with your table here to avoid that; every redundancy is a potential contradiction. However, if we have a table design optimised for querying and minimising necessary joins, it might be populated in a batch job from somewhere else, and then the design would be appropriate.

您在这里想要做的事情通常被称为水平枢纽". 我们这里缺少一些信息，因此我假设最大借用数量为2.我们需要一种机制，该机制允许我们将数据放在col1或col2中，具体取决于它是同一empno的第一行还是第二行.这就是为什么我们生成一个序列号.最后，我们将SUM(CASE seq WHEN ...)表达式与GROUP BY结合使用，以减少行数并展平表.

What you want to do here is often referred to as 'horizontal pivoting'. We lack some info here, so I'm assuming a maximum number of loans of 2. We need a mechanism that allows us to put data in col1 or col2, depending on whether it's the first or second row for the same empno. That's why we generate a sequence number. Finally, we use a SUM(CASE seq WHEN ...) expression in conjunction with a GROUP BY to reduce the number of rows and flatten the table.

在这里:

-- first global table expression - the input table
-- The table could exist already, and then this would not be needed.
WITH foo(empno,empname,loanref,amount) AS (
          SELECT  1,'abc',123,100
UNION ALL SELECT  1,'abc',456,200
)
-- second global table expression - add sequence number
-- this needs to be in the query
,    foo_numbered AS (
SELECT
  -- need a number: 1 for the first, 2 for the second loan
  ROW_NUMBER() OVER(PARTITION BY empname ORDER BY loanref) AS seq
, *
FROM foo
)
SELECT
  empno
, empname
, MAX(CASE seq WHEN 1 THEN loanref END) AS loanref_1
, SUM(CASE seq WHEN 1 THEN amount END) AS amount_1
, MAX(CASE seq WHEN 2 THEN loanref END) AS loanref_2
, SUM(CASE seq WHEN 2 THEN amount END) AS amount_2
FROM foo_numbered
GROUP BY
  empno
, empname
;

玩得开心

马可

这篇关于如何在SQL的同一行中处理具有不同值的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！