建表和视图语句：
成都网站建设哪家好，找创新互联！专注于网页设计、重庆网站建设、微信开发、成都小程序开发、集团成都企业网站定制等服务项目。核心团队均拥有互联网行业多年经验，服务众多知名企业客户；涵盖的客户类型包括：成都被动防护网等众多领域，积累了大量丰富的经验，同时也获得了客户的一致夸奖！
DROP TABLE IF EXISTS `tab`;
CREATE TABLE `tab` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NULL DEFAULT NULL,
`date` datetime NULL DEFAULT NULL,
`instructions` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`amount` decimal(18, 2) NULL DEFAULT NULL,
`type` tinyint(1) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
create view tab_v as
select
`tab`.`userid` AS `userid`,
date_format( `tab`.`date`, '%Y-%m' ) AS `ym`,
`tab`.`instructions` AS `instructions`,
`tab`.`amount` AS `amount`,
`tab`.`type` AS `type`
from
`tab`
查询语句：
select t0.userid 用户ID,
t0.ym 年月,
t1.cnt 本月收入笔数,
t2.instructions 本月最大收入项目,
t2.amount 本月最大收入金额,
t3.instructions 本月最小收入项目,
t3.amount 本月最小收入金额,
t4.cnt 本月支出笔数,
t5.instructions 本月最大支出项目,
t5.amount 本月最大支出金额,
t6.instructions 本月最小支出项目,
t6.amount 本月最小支出金额,
t7.cnt 本月结余
from (select distinct a.userid,
a.ym
from tab_v a) t0
left join (select a.userid,
a.ym,
count(1) cnt
from tab_v a
where a.type = 2
group by a.userid,
a.ym) t1
on t0.userid = t1.userid
and t0.ym = t1.ym
left join (select a.userid,
a.ym,
a.amount,
group_concat(b.instructions) instructions
from (select a.userid,
a.ym,
max(a.amount) amount
from tab_v a
where a.type = 2
group by a.userid,
a.ym) a,
tab_v b
where a.userid = b.userid
and a.ym = b.ym
and a.amount = b.amount
group by a.userid,
a.ym,
a.amount) t2
on t0.userid = t2.userid
and t0.ym = t2.ym
left join (select a.userid,
a.ym,
a.amount,
group_concat(b.instructions) instructions
from (select a.userid,
a.ym,
min(a.amount) amount
from tab_v a
where a.type = 2
group by a.userid,
a.ym) a,
tab_v b
where a.userid = b.userid
and a.ym = b.ym
and a.amount = b.amount
group by a.userid,
a.ym,
a.amount) t3
on t0.userid = t3.userid
and t0.ym = t3.ym
left join (select a.userid,
a.ym,
count(1) cnt
from tab_v a
where a.type = 1
group by a.userid,
a.ym) t4
on t0.userid = t4.userid
and t0.ym = t4.ym
left join (select a.userid,
a.ym,
a.amount,
group_concat(b.instructions) instructions
from (select a.userid,
a.ym,
max(a.amount) amount
from tab_v a
where a.type = 1
group by a.userid,
a.ym) a,
tab_v b
where a.userid = b.userid
and a.ym = b.ym
and a.amount = b.amount
group by a.userid,
a.ym,
a.amount) t5
on t0.userid = t5.userid
and t0.ym = t5.ym
left join (select a.userid,
a.ym,
a.amount,
group_concat(b.instructions) instructions
from (select a.userid,
a.ym,
min(a.amount) amount
from tab_v a
where a.type = 1
group by a.userid,
a.ym) a,
tab_v b
where a.userid = b.userid
and a.ym = b.ym
and a.amount = b.amount
group by a.userid,
a.ym,
a.amount) t6
on t0.userid = t6.userid
and t0.ym = t6.ym
left join (select a.userid,
a.ym,
sum(case
when type = 1 then
-1 * a.amount
when 2 then
a.amount
end) cnt
from tab_v a
group by a.userid,
a.ym) t7
on t0.userid = t7.userid
and t0.ym = t7.ym
只能做到这个效果了
1、一般查询语句：SELECT `lcontent` FROM `caiji_ym_liuyan`
查询数据：
2、有些时候需要查询某个字段的长度为多少时候才显示数据：
SQL语句：SELECT `lcontent` FROM `caiji_ym_liuyan` where
length(lcontent)=40
PS：在mysql中一个汉字等于3个字节，所以查询的时候需要转换一下
特别要注意的就时候对于字节的转换！
先创建一个函数
如下
CREATE FUNCTION isnum(
p_string VARCHAR(32)
)
RETURNS int(4)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT '检查字符串是否为纯数字'
BEGIN
/*检查字符串是否为纯数字*/
/*返回值：1-为纯数字 0-非纯数字*/
DECLARE iResult INT DEFAULT 0;
SELECT p_string REGEXP '^[0-9]*$' INTO iResult;
IF iResult = 1 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
然后
select * from 表名 where isnum(字段名) 0 and cast(字段名 as DECIMAL) 1000 and cast(字段名 as DECIMAL)2000
这句的表名和字段名你替换成你自己的
