create table t_items (
item_no varchar2(10) not null,
item_name varchar2(30) not null,
spec varchar2(30),
pattern varchar2(30),
category char(3) not null,
unit char(3) not null,
constraint pk_t_items primary key (item_no)
);
表2
create table t_data_dict (
id char(3) not null,
name varchar2(20) not null,
category varchar2(30) not null,
constraint pk_t_data_dict primary key (id)
);
insert into t_data_dict(id, name, category) values('A01', '一级分销商', 'client_level');
insert into t_data_dict(id, name, category) values('A02', '二级分销商', 'client_level');
insert into t_data_dict(id, name, category) values('A03', '三级分销商', 'client_level');
insert into t_data_dict(id, name, category) values('A04', '总部', 'client_level');
insert into t_data_dict(id, name, category) values('B01', '医疗器械', 'item_category');
insert into t_data_dict(id, name, category) values('B02', '中成药', 'item_category');
insert into t_data_dict(id, name, category) values('B03', '西药', 'item_category');
insert into t_data_dict(id, name, category) values('C01', '盒', 'item_unit');
insert into t_data_dict(id, name, category) values('C02', '片', 'item_unit');
insert into t_data_dict(id, name, category) values('C03', '箱', 'item_unit');
insert into t_data_dict(id, name, category) values('D01', '甲级医院', 'temi_client_level');
insert into t_data_dict(id, name, category) values('D02', '乙级医院', 'temi_client_level');
insert into t_data_dict(id, name, category) values('D03', '丙级医院', 'temi_client_level');
insert into t_data_dict(id, name, category) values('D04', '药店', 'temi_client_level');
insert into t_data_dict(id, name, category) values('D05', '其他', 'temi_client_level');
oracle中的sql语句：
select t2.* from (
select rownum as rn,t1.* from (
select a.item_no,a.item_name,a.spec,a.pattern,a.category as category_id,b.name as category_Name,
a.unit as unit_id,c.name as unit_name from t_items a,t_data_dict b,t_data_dict c where a.category=b.id and a.unit=c.id
and (a.item_no like '2%' or item_name like '2%')
order by a.item_no ) t1 where rownum<=4
) t2 where rn>1
把该语句转为mysql的sql语句
