如何在没有功能的情况下执行此操作 [英] how can I do this only with select without functions

问题描述

我有一个表客户，列fname，lname，tipo，kode

我想要一个列表所有客户用fname，tipo，批准

tipo是填充的与'老师'或'学生'

如果tipo是'老师'结果''，那么第三栏应该从tipo和kode那里取得menas，如果tipo是'学生且来自kode 4- th和5-caracter是< 10 return'false'否则返回'true'

我怎样才能选择没有函数或存储过程

I have a table customer with columns fname,lname, tipo, kode
I want a list all the customer with fname, tipo, approved
tipo is with populated with 'teacher' or 'student'
the third column should be taken from tipo and kode that menas if tipo is 'teacher' result ' ', if tipo is 'student and from the kode the 4-th and 5-th caracter are <10 return 'false' else return 'true'
how can I do this only with select without functions or stored procedures

select fname, tipo,

        (select  (case when SUBSTRING(kode, 4, 2) < 40  and tipo='student' then 'true'
                       when SUBSTRING(kode, 4, 2) <= 40 and tipo='student' then 'false'
                      else  ' '  end)  as approved from customer )
from customer
;

但返回一个错误

but returns me an error "

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

提前感谢

"
thanks in advance

推荐答案

为什么你的子查询也从同一个表中拉出并进行简单的选择？你需要简化这种方式只要想一想，你需要从客户表中得到什么。而且，你的逻辑是错误的。你有两个< 40和< = 40所以你必须自己解决这个问题。



例如：

Why is your subquery also pulling from the same table and doing simple selects? You need to simplify this way down. Just think it through in your head, what all do you need from that customer table. Also, your logic is wrong. You have both < 40 and <= 40 so you'll have to fix that part on your own.

For example:

SELECT fname, tipo, CASE WHEN SUBSTRING(kode, 4, 2) < 40 AND tipo = 'student' THEN 'true'
 WHEN SUBSTRING (kode, 4, 2) <= 40 AND tipo = 'student' THEN 'false'
 ELSE ' ' END AS approved
FROM customer

检查此... < br $> b $ b

Check This...

SELECT fname,
       tipo,
       (SELECT ( CASE
                   WHEN Substring(kode, 4, 2) < 40
                        AND tipo = 'student' THEN 'true'
                   WHEN Substring(kode, 4, 2) <= 40
                        AND tipo = 'student' THEN 'false'
                   ELSE ' '
                 END ) AS approved
        FROM   customer A Where A.fname = B.fname And A.tipo = B.tipo)
FROM   customer B;

这篇关于如何在没有功能的情况下执行此操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！