##现需要查询 Tom 这个客户在 2023 年每月的消费金额（按月份正序显示），示例如下：

drop table if exists  `trade` ; 
CREATE TABLE `trade` (
`t_id` int(11) NOT NULL,
`t_time` TIMESTAMP NOT NULL,
`t_cus` int(16) NOT NULL,
`t_type` int(2) NOT NULL,
`t_amount` double NOT NULL,
PRIMARY KEY (`t_id`));
INSERT INTO trade VALUES(1,'2022-01-19 03:14:08',101,1,45);
INSERT INTO trade VALUES(2,'2023-02-15 11:22:11',101,1,23.6);
INSERT INTO trade VALUES(3,'2023-03-19 05:33:22',102,0,350);
INSERT INTO trade VALUES(4,'2023-03-21 06:44:09',103,1,16.9);
INSERT INTO trade VALUES(5,'2023-02-21 08:44:09',101,1,26.9);
INSERT INTO trade VALUES(6,'2023-07-07 07:11:45',101,1,1200);
INSERT INTO trade VALUES(7,'2023-07-19 06:04:32',102,1,132.5);
INSERT INTO trade VALUES(8,'2023-09-19 11:23:11',101,1,130.6);
INSERT INTO trade VALUES(9,'2023-10-19 04:32:30',103,1,110);
drop table if exists  `customer` ;   
CREATE TABLE `customer` (
`c_id` int(11) NOT NULL,
`c_name` varchar(20) NOT NULL,
PRIMARY KEY (`c_id`));
INSERT INTO customer VALUES(101,'Tom');
INSERT INTO customer VALUES(102,'Ross');
INSERT INTO customer VALUES(103,'Juile');
INSERT INTO customer VALUES(104,'Niki');
-------------------------------------------------------------------
select date_format(t_time,'%Y-%m'),sum(t_amount) from trade t where
 t_cus=101  and year(t_time)=2023  and t_type=1
 GROUP BY date_format(t_time,'%Y-%m')

请查询该酒店从6月12日开始连续入住多晚的客户信息？

-- 请查询该酒店从6月12日开始连续入住多晚的客户信息？
-- 要求输出：客户id、房间号、房间类型、连续入住天数（按照入住天数升序排序）
--  条件   连续入住 即为 入住天数大于1  按照入住天数 升序排序
drop table if exists  `guestroom_tb` ; 
CREATE TABLE `guestroom_tb` (
`room_id` int(11) NOT NULL,
`room_type` varchar(16) NOT NULL,
`room_price` int(11) NOT NULL,
PRIMARY KEY (`room_id`));
INSERT INTO guestroom_tb VALUES(1001,'商务标准房',165); 
INSERT INTO guestroom_tb VALUES(1002,'家庭套房',376); 
INSERT INTO guestroom_tb VALUES(1003,'商务单人房',100); 
INSERT INTO guestroom_tb VALUES(1004,'商务单人房',100); 
INSERT INTO guestroom_tb VALUES(1005,'商务标准房',165); 
INSERT INTO guestroom_tb VALUES(1006,'商务单人房',100); 
INSERT INTO guestroom_tb VALUES(1007,'商务标准房',165); 
INSERT INTO guestroom_tb VALUES(1008,'家庭套房',365); 
INSERT INTO guestroom_tb VALUES(1009,'商务标准房',165); 

drop table if exists  `checkin_tb` ; 
CREATE TABLE `checkin_tb` (
`info_id` int(11) NOT NULL,
`room_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`` datetime NOT NULL,
`checkout_time` datetime NOT NULL,
PRIMARY KEY (`info_id`));
INSERT INTO checkin_tb VALUES(1,1001,201,'2022-06-12 15:00:00','2022-06-13 09:00:00'); 
INSERT INTO checkin_tb VALUES(2,1001,202,'2022-06-12 15:00:00','2022-06-13 09:00:00'); 
INSERT INTO checkin_tb VALUES(3,1003,203,'2022-06-12 14:00:00','2022-06-14 08:00:00'); 
INSERT INTO checkin_tb VALUES(4,1004,204,'2022-06-12 15:00:00','2022-06-13 11:00:00'); 
INSERT INTO checkin_tb VALUES(5,1007,205,'2022-06-12 16:00:00','2022-06-15 12:00:00'); 
INSERT INTO checkin_tb VALUES(6,1008,206,'2022-06-12 19:00:00','2022-06-13 12:00:00'); 
INSERT INTO checkin_tb VALUES(7,1008,207,'2022-06-12 19:00:00','2022-06-13 12:00:00'); 
INSERT INTO checkin_tb VALUES(8,1009,208,'2022-06-12 20:00:00','2022-06-16 09:00:00');
INSERT INTO checkin_tb VALUES(9,1009,208,'2022-08-12 20:00:00','2022-08-16 09:00:00');
---------------------------------------------------------
select
    *
from
    (
        SELECT
            user_id,
            tb.room_id,
            t.room_type,
            DATEDIFF(tb.checkout_time, tb.checkin_time) days
        from
            checkin_tb tb
            left join guestroom_tb t on tb.room_id = t.room_id
        where
            tb.checkin_time >= '2022-06-12 00:00:00'
        order by
           days asc, tb.room_id asc, tb.user_id desc
    ) tq
where
    tq.days > 1

sql 行专列

CREATE TABLE public.t_score (
  id int4 NOT NULL,
  stu_name varchar(255),
  subject varchar(255) ,
  score numeric(20,2),
  CONSTRAINT t_score_pkey PRIMARY KEY (id)
)
;
select 
    t.stu_name ,
    sum( case when t.subject='chinese' then t.score else 0 end) as chinese,
    sum( case when t.subject='math' then t.score else 0 end) as math
  from t_score  t GROUP BY  t.stu_name

mysql 数据导出为 csv

--  my.cnf  or   my.ini   设置如下
--  secure-file-priv="D:/tmp"  
SELECT id
FROM idms_user
WHERE id>100001
LIMIT 1000
INTO OUTFILE 'd:/tmp/datatest.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ''
LINES TERMINATED BY '\n';

统计用户 每天的登录次数

select
	t.id '用户id',
	t.name '用户名称',
	q.login_time '日期',
	q.login_size '登录次数'
	
from
	sys_tuser t
inner join (
	select
		t2.userid,
		date_format(t2.login_time, '%Y-%m-%d') login_time  ,
		count(1) login_size
	from
		sys_login_log t2
	group by
		t2.userid ,
		date_format(t2.login_time, '%Y-%m-%d') ) q on
	t.id = q.userid;

postgres 修改字符集

## 修改字符集Wie中文
update pg_database set (encoding, datctype, datcollate)=(6, 'zh_CN.UTF-8','zh_CN.UTF-8') where datname = 'test1';
## 创建模板库
CREATE DATABASE test3 WITH TEMPLATE test1 OWNER postgres;

##  查询数据库 字符集编码信息
select datname,datcollate,datctype from pg_database where datname = 'test';