数据库知识点

木易lg

已于 2022-11-23 10:31:39 修改

阅读量355

点赞数

文章标签：数据库

于 2022-11-22 17:01:43 首次发布

本文链接：https://blog.csdn.net/m0_46087798/article/details/127984843

版权

1、数据库一组数据取某一条数据

全部无关数据取最早一条

select * from cms.acct where cust_id=63162 order by create_date limit 1

一组数据（同一用户）取一条数据

方法一：窗口函数

WITH summary AS (
    SELECT a.acct_id, a.acct_code, a.acct_name, a.cust_id, a.status, a.create_date, a.status_date,
           ROW_NUMBER() OVER(PARTITION BY a.cust_id ORDER BY a.create_date DESC) AS ranks
      FROM cms.acct a)
SELECT s.*
  FROM summary s
 WHERE s.cust_id in(63163,63162) and s.ranks = 1

方法二：通用方法（嵌套select）

SELECT
      x.acct_id, x.acct_code, x.acct_name, x.cust_id, x.status, x.create_date, x.status_date
FROM cms.acct x
JOIN (SELECT p.cust_id,MAX(create_date) AS max_create_date
      FROM cms.acct p
      GROUP BY p.cust_id) y ON y.cust_id = x.cust_id
                             AND y.max_create_date = x.create_date
where x.cust_id in(63163,63162)
GROUP BY x.acct_id , x.cust_id, x.create_date

方法三：pgsql特有 DISTINCT ON 取组内数据

SELECT DISTINCT ON (cust_id)
		acct_id, acct_code, acct_name, cust_id, status, create_date, status_date
		FROM cms.acct
	where cust_id in(63163,63162) and status = '00A'
		ORDER  BY cust_id,create_date desc

特别注意：DISTINCT ON ( expression [, …] )把记录根据[, …]的值进行分组，分组之后仅返回每一组的第一行。需要注意的是，如果你不指定ORDER BY子句，返回的第一条的不确定的。如果你使用了ORDER BY 子句，那么[, …]里面的值必须靠近ORDER BY子句的最左边。例子中的cust_id

结果：

2、coalesce函数

SELECT 
		acct_id, acct_code, acct_name, cust_id, status, create_date, status_date,
		coalesce(acct_code,'123')|| ','||coalesce(acct_name,'')|| ','||coalesce(create_date::VARCHAR,'') as strings
		FROM cms.acct
	where acct_id =63163
			and status = '00A'
		ORDER  BY cust_id,create_date desc
--日期要进行转化

COALESCE()函数定义：返回列表中第一个非null表达式的值。如果所有表达式求值为null，则返回null

结果：

3、case函数

SELECT cust_code,cust_id,cust_name,status,
		case
		when sale_dep_level0 = '62' then '62'
		when sale_dep_level0 = '64' then '64'
		when sale_dep_level0 = '71' then '71'
		when sale_dep_level0 = '86' then 'Base100'
		else '100' end as "dic_code"
from cust where cust_id = 14141

等价于：

for (CustApiVo custApiVo : custVo) {
            if(Utils.notEmpty(custApiVo.getSaleDepLevel0())){
                int saleDepLevel0 = custApiVo.getSaleDepLevel0().intValue();
                switch (saleDepLevel0) {
                    case 62:
                        custApiVo.setBuTenantCode("64");
                        break;
                    case 71:
                        custApiVo.setBuTenantCode("71");
                        break;
                    case 86:
                        custApiVo.setBuTenantCode("BU0500");
                        break;
                    default:
                        custApiVo.setBuTenantCode("100");
                        break;
                }
            }else{
                custApiVo.setBuTenantCode("100");
            }
        }