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

2010-10-02 11:21:47 257 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

零基础搭建机械零件检测系统：基于YOLOv8的PySide6可视化开发全攻略本文详细介绍了如何从零开始搭建基于YOLOv8的机械零件检测系统，涵盖环境配置、数据集准备、模型训练优化及PySide6可视化界面开发全流程。通过实战案例展示YOLOv8在工业检测中的高效应用，帮助开发者快速实现自动化零件检测解决方案。阅读详情

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

...全文

保姆级教程：在K230开发板上为张大头步进电机实现位置、速度、回零全功能控制本文提供了一份详细的K230开发板与张大头步进电机的控制教程，涵盖硬件连接、协议解析、模块化代码实现及实战案例。通过位置、速度和回零功能的全面讲解，帮助开发者快速掌握步进电机的高级控制技术，适用于自动化、机器人等应用场景。阅读详情

佬侽孩

Web 开发 81,108 成员 341,721 内容

+ 关注 + 加入社区

切换为时间正序

kebin0001 2010-10-02

打赏
举报

這是做分頁的查詢
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

佬侽孩 2010-10-02

打赏
举报

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

thegodofwar 2010-10-02

打赏
举报

[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写法并没有错

thegodofwar 2010-10-02

打赏
举报



 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;

佬侽孩 2010-10-02

打赏
举报

/**
*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为外语系，可是查询结果为空，达不到想要的效果啊，杂回事呢？

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

相关推荐

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