挑战 一个oracle转mysql的sql语句（某公司笔试题目）

[佬侽孩]

表1
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语句

[佬侽孩]

/**
*database:bookManager
* password:root
*
**/
drop database if exists bookManager;

create database bookManager;

use bookManager;

drop table if exists a_article;
drop table if exists a_datadict;
表一
create table if not exists a_article
(

articleId int not null auto_increment,
articleName varchar(20) not null,
grade varchar(20),
departmentId varchar(30) not null,
t_categoryId varchar(30) not null,
create_date datetime,
filename varchar(30),
constraint pk_a_article primary key (articleId )
);

表二
create table if not exists a_datadict
(
id varchar(30) primary key not null,
name varchar(20) not null,
category varchar(30) not null

);

insert into a_datadict values("A01","计科系","department");
insert into a_datadict values("A02","中文系","department");
insert into a_datadict values("A03","外语系","department");
insert into a_datadict values("A04","管科系","department");
insert into a_datadict values("A05","法学系","department");
insert into a_datadict values("A06","美术系","department");
insert into a_datadict values("A07","体育系","department");
insert into a_datadict values("A08","音乐系","department");
insert into a_datadict values("B01","老师","t_category");
insert into a_datadict values("B02","学生","t_category");
insert into a_datadict values("B03","其他","t_category");

防上面的题目，自己在mysql中建的表

sql语句如下
1.mysql中的第一种写法
select a.articleId,a.departmentId,b.name from a_article as a inner join a_datadict as b on a.departmentId=b.id;

2.mysql中的第二种写法
select a.articleId,a.departmentId,b.name from a_article as a ,a_datadict as b where a.departmentId=b.id;

以上两种写法都达不到想要的结果，结果都为空，不知道是咋回事

显示的结果为空 但是表一，表二中都有数据的，且表一中的departmentId为A03，对应数据字典中的A03的name为外语系，可是查询结果为空，达不到想要的效果啊，杂回事呢？

[thegodofwar]

 select 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 limit 4;

[thegodofwar]

[Quote=引用 2 楼 yimu0214 的回复:]
/**
*database:bookManager
* password:root
*
**/
drop database if exists bookManager;

create database bookManager;

use bookManager;

drop table if exists a_article;
drop table if exists a……
[/Quote]
表2没有外键参考表1，两种sql写法并没有错

[佬侽孩]

楼上的mysql 一定要设定外键约束才行吗？之前用的是oracle的，好像不用，mysql没仔细研究过

[kebin0001]

這是做分頁的查詢
MYSQL可以直接用 limit來達到
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%' limit 0,4